2

PostgreSQL from 8.3 knows Enumerated Types.

Simple question: Does 8.2(.9) know them too? If not, what can be used as replacement?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
grssnbchr
  • 2,877
  • 7
  • 37
  • 71

2 Answers2

5

Enumerated types are not supported in 8.2.

As an alternative, use a text column with a foreign key reference to a table that lists possible values.

CREATE TABLE fruit_enum (
    fruit text primary key
);

INSERT INTO fruit_enum(fruit) VALUES
('apple'),('pear'),('grape');

CREATE TABLE basket (
    -- ... blah columns ...
    fruit text,
    -- ... blah columns ...
    CONSTRAINT fruit_fk FOREIGN KEY (fruit) REFERENCES fruit_enum(fruit)
);

This is the classic relational formulation, and is usually preferable to using an enum anyway. Enumerations are painful to deal with from some client applications and Pg doesn't currently support removing values from them either.

Here's how the above works:

regress=# INSERT INTO basket(fruit) VALUES ('apple');
INSERT 0 1
regress=# INSERT INTO basket(fruit) VALUES ('cider');
ERROR:  insert or update on table "basket" violates foreign key constraint "fruit_fk"
DETAIL:  Key (fruit)=(cider) is not present in table "fruit_enum".
regress=#

If you like you can indirect the enumeration via an integer ID, where the foreign key is to the enumeration table's ID not the enumeration value. Personally I'm not a fan of that approach, even though it's really only a true "enumeration" that way, because getting the value always requires an expensive JOIN or subquery, and there's typically little space saved.

Alternately, as @a_horse_with_no_name pointed out, a simple CHECK constraint is often sufficient:

CREATE TABLE basket (
    -- ... blah columns ...
    fruit text,
    -- ... blah columns ...
    CONSTRAINT fruit_in_allowed_list CHECK (fruit IN ('apple', 'pear', 'grape'))
);

Like the foreign key approach (but unlike enums) this is SQL-standard and should work on any database.

Also, seriously, 8.2? That's end-of-life and woefully, amazingly out of date. Make urgent upgrade plans.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Good points! If the list of values is *really* static, one could also think about a check constraint. –  Sep 18 '12 at 15:01
  • @a_horse_with_no_name Gah, I should've thought of that. Added. – Craig Ringer Sep 18 '12 at 23:12
  • Thanks for this exhaustive answer. We are using CentOS 5 which guarantees us a lot of stability and this comes with PostgreSQL 8.2. We know that it's out of date and are considering upgrading to CentOS 6. – grssnbchr Sep 19 '12 at 07:49
  • @wnstnsmth Why not just upgrade PostgreSQL on CentOS 5? http://yum.postgresql.org/packages.php – Craig Ringer Sep 19 '12 at 09:00
  • We are actually considering this as well. The thing is that we have a lot of stuff that depends on PostgreSQL which we maybe would have to update as well.. but yes - we know we're out of date and we are trying to go forward asap ;-) – grssnbchr Sep 19 '12 at 09:02
1

Enums were introduced in 8.3
Here are the release notes: http://www.postgresql.org/docs/8.3/static/release-8-3.html

The data types for 8.2 are documented here: http://www.postgresql.org/docs/8.2/static/datatype.html

But you shouldn't use 8.2 anyway (actually I wouldn't recommend 8.3 either)