Re: Retrieve columntypes and checks?

--- Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de> wrote:

>
> 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".

If there is a small number of allowed values, a check
constraint may be most convenient.

Jesper, a full-featured RDBMS like PostgreSQL provides
lots of capability to validate your data in the
backend (including referential integrity, as Oliver
mentioned). This protects your data against
programming mistakes, and frees your application
programmer to concentrate on the specifics of the
application. You will gain by spending some time
reading about it. Any good book on SQL will give you
an introduction.

>
> Best regards
> Oliver
>
> --
> Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr.
> 2, 80538 Mnchen
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>

_______________________________
Do you Yahoo!?
Express yourself with Y! Messenger! Free. Download now.
http://messenger.yahoo.com