Skip site navigation (1) Skip section navigation (2)

Re: Retrieve columntypes and checks?

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org, Jesper Krogh <jesper(at)krogh(dot)cc>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Retrieve columntypes and checks?
Date: 2004-10-28 14:25:24
Message-ID: 20041028142524.99527.qmail@web20824.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- 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

In response to

Responses

pgsql-novice by date

Next:From: Sample, Matt (GE Healthcare)Date: 2004-10-28 15:20:46
Subject: Bulk Loading into posgres 8.0.0
Previous:From: Karsten HilbertDate: 2004-10-28 13:56:27
Subject: Re: Question Regarding Locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group