The Difference In Ordering Of Enum Type Literals Between Postgresql 9.0 And 9.1

There has been some curious update in the way enum types work between PostgreSQL 9.0 and 9.1. The pg_catalog.pg_enum table has a new column enumsortorder in PostgreSQL 9.1. This or

Solution 1:

I think you'll need to check the PostgreSQL version and change behaviour appropriately, or use SQL that doesn't touch the catalog to determine the ordering.

An idea for the latter, given dummy enum:

CREATE TYPE test_enum AS ENUM ('z','x','y');
ALTER TYPE test_enum ADDVALUE'a' BEFORE 'x';

is to ORDER BY the cast of the enum label to values of the enum type using the row_number window function available in 8.4 and newer:

SELECT enumlabel, row_number() OVER (ORDERBY enumlabel::test_enum) AS sort_key
FROM pg_catalog.pg_enum
WHERE enumtypid ='test_enum'::regtype;

This gets you the labels ordered by a sort key. In older Pg versions Pg will just sort by the oid of the enum values, in newer versions it'll use the enumsortorder, but you don't have to care either way, you've just told PostgreSQL "sort these into the correct order please".

Or if you just need them in the order the server expects, write:

SELECT enumlabel
FROM pg_catalog.pg_enum
WHERE enumtypid = 'test_enum'::regtypeORDERBY enumlabel::test_enum