PostgreSQL ENUM?

[mithril]

Looked through the Postgres manual, and I'm sure I just overlooked something obvious, but what is the Postgres equivalent to MySQL's ENUM datatype ? Thanx!!

-geoff

[[deleted]]

ENUM types just refuse to take a value other than what you defined. If your script is in order it will never offer to store anything else (because that will cause it to fail)

But if you need an ENUM type anyway (you can never be too carefull) you can add the options into a new table, and use a constraint to force referencial integrety.

This means that the column you put the constraint on can only take the values that are present in the table you reference.

This is a bit of an in-depth doc, but I think it exmplains it:
http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php

[mithril]

Great! Thanx Vincent.... I knew what an ENUM was and I am currently using it in a couple cases (MySQL) to do what you spoke of and force valid data even if my script borks. Joining on another table to force these checks seems like overkill for my current purposes so I'll prolly just drop the ENUM for a CHAR.

-geoff

[[deleted]]

Hold your horsies there Geoff, a referencial integrety constraint is not the same thing as a JOIN.

It is an internal thing. The only thing you see from a query point of view is that your query will fail if you insert the wrong value. The only diff is in where the database get's the set of 'valid' values.
MySQL get's them from a special field parameter, and PostgreSQL gets them from a seperate table.

That does mean that in PostgreSQL you can add new values with regular SQL, and more importantly, you cannot remove a value if it is still being used by the table with the constraint. Unlike MySQL where you can change the ENUM set and destroy all your data.

[mithril]

Thanx again Vincent. My newbie status with regards to PostgreSQL is clearly shining on through🙂

-geoff

[[deleted]]

har har, another newbie uncovered... moehahaha