Thread: Retrieve columntypes and checks?
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? Thanks. -- ./Jesper Krogh, jesper@krogh.cc
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
--- Oliver Fromme <olli@lurza.secnetix.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 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > _______________________________ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com
I gmane.comp.db.postgresql.novice, skrev Jeff Eckermann: > 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. You could argue that way, but when used in a perl/CGI script i really think it's nice for the users to have some validation of data in JavaScript instead of needing to reload the hole page just to validate data. Jesper -- ./Jesper Krogh, jesper@krogh.cc Jabber ID: jesper@jabbernet.dk
On Thu, Oct 28, 2004 at 10:27:30 +0000, Jesper Krogh <jesper@krogh.cc> 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? A possible approach would be to use domains. That is probably a good practice for this case anyway, since if an "enum" is used in more than one table, you can have the constaint in one place. You should be able to get the domain name associated with a column, but I don't know whether or not this will require extra queries.
On Thu, Oct 28, 2004 at 10:27:30AM +0000, Jesper Krogh wrote: > > 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.. Others have suggested that you let the database do the checking for you. But if you want to see what the column types and constraints are, you could query the system catalogs: http://www.postgresql.org/docs/7.4/static/catalogs.html http://www.postgresql.org/docs/7.4/static/information-schema.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/