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 (AT) krogh (DOT) cc



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Jesper Krogh wrote:
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

---------------------------(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)

--- Oliver Fromme <olli (AT) lurza (DOT) secnetix.de> wrote:

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.




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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

I gmane.comp.db.postgresql.novice, skrev Jeff Eckermann:
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 (AT) krogh (DOT) cc
Jabber ID: jesper (AT) jabbernet (DOT) dk



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, Oct 28, 2004 at 10:27:30AM +0000, Jesper Krogh wrote:
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/s...on-schema.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(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)

On Thu, Oct 28, 2004 at 10:27:30 +0000,
Jesper Krogh <jesper (AT) krogh (DOT) cc> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster