Enumerated Fields in PostgreSQL
Pages: 1, 2

Enter EnumKit

With all this in mind I created EnumKit for PostgreSQL. It works with PostgreSQL 8.0 and later. It requires installation privileges for your database share/ and lib/ directories, too. Basically, it is an attempt to provide a facility for enums that have the ease of use of the check methods and the correctness of the lookup tables, plus high efficiency. The downside is that for each enum type you want to create, you have to do a little compilation and installation. Fortunately, it's not very hard, and you don't have to write any C--I've done that part for you.

Here's how the seasons example looks with EnumKit:

make TYPENAME=seasons ENUMS=' "spring", "summer", "autumn", "winter" '
make TYPENAME=seasons install
psql -f /path/to/your/pgsql/contrib/seasons-install.sql yourdbname

The quotes are important here, and if you muck them up you will get a compilation error. The values must be a comma-separated list of double-quoted strings. Make sure to enclose the whole list in single quotes.

Now you can just use the new type like in the domain example above, but PostgreSQL stores it efficiently (as a 16-bit integer), and sorts and compares it in the desired order.

create table average_temperature
(
     year     integer,
     temp     real,
     season   seasons
);
insert into average_temperature values(1999,56.7,'winter');
insert into average_temperature values(1999,78.3,'summer');
select * 
    from average_temperature
    order by year, season;

Modifying the Type

One possible downside of using an EnumKit-created type is that modifying the type is at best dangerous. Once it is in place and you have used it, you really should not touch it. What if you need to modify it? The best way is to create a new type that incorporates the modifications, and then modify each table that uses the type. Luckily, modern versions of PostgreSQL have extremely good support for modifying table columns. Here is an example where I defined a column with an rgb enumeration type, and then changed it to a rainbow enumeration type. Note that although several values (notably blue and green) change position in the enumeration order in the change, their values remain.

andrew=# create table foo (i serial, c rgb);
NOTICE:  CREATE TABLE will create implicit sequence "foo_i_seq" for serial
    column "foo.i"
CREATE TABLE
andrew=# insert into foo (c) values ('blue');
INSERT 8711471 1
andrew=# insert into foo (c) values ('green');
INSERT 8711472 1
andrew=# insert into foo (c) values ('red');
INSERT 8711473 1
andrew=# select * from foo order by c;
 i |   c   
---+-------
 3 | red
 2 | green
 1 | blue
(3 rows)

andrew=# insert into foo (c) values ('yellow');
ERROR:  invalid input value for enum: "yellow"
andrew=# alter table foo alter column c type rainbow using c::text;
ALTER TABLE
andrew=# select * from foo order by c;
 i |   c   
---+-------
 3 | red
 2 | green
 1 | blue
(3 rows)

andrew=# insert into foo (c) values ('yellow');
INSERT 8711477 1
andrew=# select * from foo order by c;
 i |   c    
---+--------
 3 | red
 4 | yellow
 2 | green
 1 | blue
(4 rows)

Underneath the Hood

Starting with PostgreSQL version 8.0, there is available a facility called PGXS that makes creating and installing PostgreSQL extensions a whole lot easier. The PGXS documentation describes it in some detail. Basically, you put a few entries into a makefile and PGXS will do most of the heavy lifting for you. I have used this in EnumKit, with a little twist to make it possible to define not one extension module but a family of them, one for each enumeration type.

This is just one example of a PostgreSQL extension module--probably one of the simplest you will see. PostgreSQL is extensible by design, and there are many examples of extension modules around, including functions, types, and server-side programming languages. The extensibility of PostgreSQL is one of its great strengths and makes it a joy to work with.

Essentially, EnumKit makes a copy of generic.c and generic-install.sql with the type name and values plugged in, and then compiles the C file into a loadable module. The SQL file is necessary to set up the type in a particular database.

In the Future

The next step is really to remove the requirement for an extension module. Ideally, you should be able to do all this at the SQL level without having to mess with makefiles, privileges, etc. Any user should be able to define an enumeration type and use it as effectively and easily as they can with types created by EnumKit. Thus, EnumKit is really the result of some experimentation and a bit of a stop-gap.

How to do enums right (or even whether to do them at all) has been the subject of ongoing debate in the PostgreSQL community. As usual, this debate has taken place in the open on the PostgreSQL Hackers mailing list (available from the PostgreSQL mailing list archives).

However, there seems to be general agreement that it is worth doing. I intend to work on it for the 8.2 release. In the meantime, the techniques presented in this article should be able to satisfy most needs.

Andrew Dunstan works for a small consulting and software company in the Triangle area of North Carolina, and contributes to PostgreSQL as an enthusiastic hobbyist as well as a sometime professional user.

---

Return to the Databases DevCenter