PostgreSQL is a very flexible database system. Its flexibility derives from its way of storing metadata. Unlike many other databases, Postgres involves no “magic”: Every database object — table, type, function or cast — is described by a row in a special table, called a system catalog. There are multiple system catalogs: pg_class stores information about tables, pg_type describes types. And from pg_proc one can extract all properties of functions — including, for those written in interpreted languages, the source code. One can also obtain exhaustive information about the enum types:

okliukin=# SELECT * FROM pg_enum WHERE enumtypid = 'city'::regtype;
 enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
     16481 |             1 | Berlin
     16481 |             2 | Dortmund
     16481 |             3 | Dublin
(3 rows)

It might be tempting to modify the database metadata by using DML commands to change the values in the catalogs. But this is dangerous, and likely to cause data loss. Let’s explore some commands that you should not try to run in production.

Let’s try to add a new label directly. Before PostgreSQL introduced ALTER TYPE ADD VALUE syntax for enums in version 9.1, this sort of statement was very popular. It is still used from time to time, like this:

okliukin=# INSERT INTO pg_enum VALUES('city'::regtype, 4, 'Brieselang');
INSERT 16487 1

And as immediately used in a table:

okliukin=# CREATE TABLE meeting(m_id serial, m_city city, m_time timestamp);
CREATE TABLE
…
postgres=# select * from meeting order by m_city;
 m_id |   m_city   |           m_time
------+------------+----------------------------
    1 | Dublin     | 2015-07-14 07:50:34.976061
    2 | Brieselang | 2015-07-07 13:50:34.976061
(2 rows)

Why would Dublin be ahead of the Brieselang, you may ask? The answer is in the ‘enumsortorder’ column, which defines how the values of the given enum are sorted. By naively updating the catalog and ignoring the implementation details, we made the database system deliver results that one would not normally expect.

Now, what if we try to modify the sort order?:

okliukin=# UPDATE pg_enum SET enumsortorder = 1 WHERE enumtypid = 'city'::regtype  and enumlabel = 'Brieselang' ;
ERROR:  duplicate key value violates unique constraint "pg_enum_typid_sortorder_index"
DETAIL:  Key (enumtypid, enumsortorder)=(16481, 1) already exists.

Alas, sort order positions are unique for any given type (*). We may discover that there is a proper command to add a new type value, i.e.:

okliukin=# ALTER TYPE city ADD VALUE 'Brieselang' AFTER 'Berlin';
ERROR:  enum label "Brieselang" already exists

As one can see from the output of the command above, enum labels are unique as well.

A brave DBA might decide to remove the old value altogether and add a new one in the correct place. It would be nice to have an ALTER TYPE DELETE VALUE command. Unfortunately, enums still aren’t first-class citizens in the PostgreSQL world, and there is no way to remove or rearrange values. Since there is no matching SQL command, a DBA might decide to modify system catalogs directly:

okliukin=# DELETE FROM pg_enum WHERE enumtypid = 'city'::regtype AND enumlabel = 'Brieselang';
DELETE 1

Adding the new value works:

okliukin=# ALTER TYPE city ADD VALUE 'Brieselang' AFTER 'Berlin';
ALTER TYPE

And we can visualise the new content of pg_enum:

okliukin=# SELECT * FROM pg_enum WHERE enumtypid = 'city'::regtype;
 enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
     16481 |             1 | Berlin
     16481 |             2 | Dortmund
     16481 |             3 | Dublin
     16481 |           1.5 | Brieselang
(4 rows)

Another interesting implementation detail: the sort order column has a floating-point type that allows you to add new values in the middle of the enum without changing any existing ones.

But now, when we select data from our table, we get this:

okliukin=# SELECT * FROM meetings;
ERROR:  invalid internal value for enum: 16487

What happened, and where does the value 16487 come from?

For many system catalogs, PostgreSQL actually stores a hidden column called OID. This integer column contains a cluster-wide unique number in direct correspondence to the row it represents. One can get this value by including OID directly in the select statement. As such, columns are not covered by ‘SELECT *’:

okliukin=# select oid, * from pg_enum ;
  oid  | enumtypid | enumsortorder | enumlabel
-------+-----------+---------------+------------
 16482 |     16481 |             1 | Berlin
 16484 |     16481 |             2 | Dortmund
 16486 |     16481 |             3 | Dublin
 16495 |     16481 |           1.5 | Brieselang
(4 rows)

Here there is no value with OID 16487—it corresponds to the label ‘Brieselang’ that we inserted manually and then deleted. The ‘meeting’ table does not store the enum values directly; instead, it contains references to the OIDs of the enum labels in the pg_enum catalog. We can change the OID for the Brieselang label to match the one stored in the ‘meeting’ table:

okliukin=# UPDATE pg_enum SET oid = 16487 WHERE enumtypid = 'city'::regtype AND enumlabel = 'Brieselang';
ERROR:  cannot assign to system column "oid"
LINE 1: UPDATE pg_enum SET oid = 16487 WHERE enumtypid = 'city'::reg...

OID columns do not allow assignment of values by users, so we are stuck. You can rescue some data by omitting the enum values that correspond to the ‘deleted’ labels, i.e.

okliukin=# SELECT * FROM meetings WHERE m_city IN (SELECT enumlabel::city FROM pg_enum WHERE enumtypid = 'city'::regtype AND oid != 16487);
 m_id |   m_city   |           m_time
------+------------+----------------------------
    1 | Dublin     | 2015-07-14 08:14:07.288819
(1 row)

In general, however, there is no obvious way to actually retrieve the data in the m_city column for all table rows without either hacking PostgreSQL source code or changing the binary data in the physical files representing the table content. It might be an easy recovery task in our example, where we lost only one label. But what if your enum has tens or hundreds of labels, each representing a state machine for the order, and you delete and then reinsert half of them — only to change their order? (**) This is why one should never modify PostgreSQL system catalogs directly.

“*” As a side note, even if the UPDATE would succeed, the new sort order will not emerge in the same session due to caching effects. The ALTER TYPE ADD VALUE command, on the other hand, does it properly. In addition, PostgreSQL documentation states that the new OID should be an odd number; otherwise, the ‘enumsortorder’ column is ignored. The correct solution to the problem of changing the sort order of enum values is to first create a new enum type with the same labels but a different order, then do type conversion for the table columns using the old enum values USING ::text::new_enim, and finally rename the new enum value to the old one — dropping (or renaming) the old one beforehand.

“**” You can change the data type of the pg_enum column to oid in the pg_attribute system catalog, update the oid values of the enum labels that cause the error to the values, and then change the pg_attribute type back, but see the title of this blog post.