Corey Coogan

Python, .Net, C#, ASP.NET MVC, Architecture and Design

  • Subscribe

  • Archives

  • Blog Stats

    • 110,056 hits
  • Meta

Archive for the ‘Database’ Category

Adding to an Enum in Postgres 8.x and 9.0

Posted by coreycoogan on April 4, 2011


Postgres has an emum type that I’m quite fond of. To create one, you just something like this:

CREATE TYPE order_status AS ENUM ('Pending','Processing','Shipped');

Now let’s say that I realize later I also need an order status for BackOrdered. In PG 9.1, adding an enum value becomes much simpler, but for those of us on earlier versions, we’ll have to do this the hard way.

Postgres stores enum values in the pg_enum system table. It stores the enum types in the pg_type table. For user-defined types, such as an enum, PG will prefix the name with an underscore. So my enum type of “order_status” is actually stored by PG as “_order_status”. Knowing this, I can simply insert my new value into the pg_enum table, using the numeric ID used by PG for the special underscore name.

Here’s the result:

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'BackOrdered' FROM pg_type WHERE
    typname='_order_status';

This will put the new value at the end of the enum. If you care where it goes, and I can’t see a reason why you would, you’ll have to resort to some other trickery. I learned this method, as well as ways to change the order, from this post on Stack Overflow.

Advertisements

Posted in Database, Postgres | Tagged: , , , | Comments Off on Adding to an Enum in Postgres 8.x and 9.0

Use Excel to Automate Data Entry

Posted by coreycoogan on January 27, 2011


Over the course of my career, I have frequently been given random excel spreadsheets that contain data that needs to be either inserted or updated to a database. There are many routes one can go with this task, most of which are more complicated then necessary. I find the simplest solution is to use Excel formulas to generate my SQL statements for me.

  1. Create a formula for the SQL statement in a blank cell of the spreadsheet’s first row of data.
  2. Copy and paste the formula to the same blank cell in every other row. This fastest way I know to do the paste is to: select the first cell you wish to paste.  Hold down CTL+SHIFT+END.  This will select everything from the point of selection to the last row and last cell in the spreadsheet.  At this point, continue to hold down shift and arrow back to the left to get to the original column and hit enter.

    With the cursor on the cell with the formula, Excel outlines the cell with a tiny square on the bottom right corner.  Double click on that tiny square and your formula is repeated all the way to where the data ends next to it.
    (Thanks to Todd Boehm for posting this to the comments)

    The formula will now be copied to each row.

  3. Select every row that contains the sql and paste into your database query window.
  4. Run the queries and you are done.

An Example

Given the following spreadsheet for some customers, I will show a simple formula to do an insert into the customer table.

A B C D
1 NAME PHONE SIGNUP_DATE
2 Joe Smith (920) 555-1112 5/21/2010 = “insert into CUSTOMER (name,phone,signupDate) values (‘” & A2 & “‘, ‘” & B2 & “‘, ‘” & TEXT(C2,”M/dd/yyyy”) & “‘)”

Convert Excel Date to Text or String

Notice the use of the TEXT function against the value for SIGNUP_DATE.  This is necessary with dates because Excel will spit that value out in a serial format, which isn’t what we want in our database.  Use the TEXT function to convert the Excel Date to text for the insert.

Posted in Business, Database, Helpful Tips | Tagged: , , , | 1 Comment »

How to Move PostgreSql Tables to a Different Schema

Posted by coreycoogan on December 22, 2010


Recently an application I’m working on had the need to move a mess of tables from the PG default “Public” schema to a new one, which we’re calling “Selection”. The more recent versions of PG (8.2 as of this writing) have a SET SCHEMA action available on the ALTER TABLE command.

ALTER TABLE name SET SCHEMA new_schema

SET SCHEMA is very nice in that it not only moves the tables, but any associated indexes, sequences and constraints.

From the 8.2 Documentation:

This form moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well.

This is all fine and good, but if you have more than a couple tables to move, this can be hassle. Here’s how to use PG’s pg_tables System Table to generate the sql for every table in your database. Run this query against the database that has the tables you wish to move. Then simply copy/paste the output into another query window and execute. Don’t forget to make the schema first!

select 'alter table "' || tablename || '" set schema "Selection";' 
from pg_tables  
where schemaname='public';

Posted in Database | Tagged: , , , , | 1 Comment »