Corey Coogan

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

  • Subscribe

  • Archives

  • Blog Stats

    • 111,234 hits
  • Meta

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

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.


Sorry, the comment form is closed at this time.

%d bloggers like this: