Re: Retrieve columntypes and checks?

Jesper Krogh wrote:
> Trying to convert an application from MySQL to PostgreSQL I ran into
> some troubles..
>
> In MySQL is it quite easy to find out which type a column is.. in
> particular if a column is an "enum" so the webapplication can do
> appropiate checks in javascript in advance for an insert.
>
> In PostgreSQL the enum's are converted to varchar with checks, so I need
> to get a hold on the column-type and if it's a varchar parse the check
> string for values..
>
> How do I do that?
>
> Any better suggestions?

Instead of using a check string, put the possible values of
the "enum" into a separate table, and define the varchar
column to be a foreign key into that table. (If you're not
familiar with the concept of foreign keys, please read the
appropriate section in the PostgreSQL docs. Foreign keys
are an extremely useful thing.)

Two advantages of that approach:

- PostgreSQL will automatically perform the checks, so you
don't have to do any checking yourself.

- If you want to perform checks yourself (e.g. in a GUI
frontend), you can simply retrieve all valid values by
SELECTing from the "enum table".

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"UNIX was not designed to stop you from doing stupid things,
because that would also stop you from doing clever things."
-- Doug Gwyn