Thread: enum
As you guys can see, I'm in the process of migrating from mysql to postgres. My next question to you guys is how does one implement the mysql enum type. Also, when I issue the \dT command, I get this nice list of data types. Are all those data types standard SQL data types? or are some postgres "extensions"? Cheers. Steve.
In replying to my own post, I realize that a mysql enum is equal to a varchar with a constraint on the value of the string inserted into the column, (which is what I want to do, make sure only 2 or 3 different strings can stored in the varchar column.) But I'm having a hard time finding the syntax for the column constraint entry in the create table command. anyone can let me know where I can find info on the constraint syntax? I've looked through the postgresql manuals and haven't found anything. Thanks again. Steve. On Sat, 2002-11-30 at 12:13, Adler, Stephen wrote: > As you guys can see, I'm in the process of migrating from mysql to > postgres. My next question to you guys is how does one implement > the mysql enum type. Also, when I issue the \dT command, I get > this nice list of data types. Are all those data types standard > SQL data types? or are some postgres "extensions"? > > Cheers. Steve. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Define the constraint table:
create table "myConstraints"
(
"myConstraint" varchar(32) not null primary key
)
in the referencing table declare the constrained column:
"constraintColumn" varchar(32) not null references "myConstraints" on
update cascade,
Of course the size of the varchar is up to you, but should be the same in
both tables. The "on update cascade" allows you to change the value of the
"myConstraint" column in "myConstraints" and have it update the values for
those matching entries in the "constraintColumn" in the referencing table.
HTH
Rod
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Adler, Stephen
> Sent: Saturday, November 30, 2002 12:49 PM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] enum
>
>
> In replying to my own post, I realize that a mysql enum is
> equal to a varchar with a constraint on the value of the
> string inserted into the column, (which is what I want to do,
> make sure only 2 or 3 different strings can stored in
> the varchar column.) But I'm having a hard time finding
> the syntax for the column constraint entry in the create
> table command. anyone can let me know where I can find info
> on the constraint syntax? I've looked through the postgresql
> manuals and haven't found anything.
>
> Thanks again. Steve.
>
> On Sat, 2002-11-30 at 12:13, Adler, Stephen wrote:
> > As you guys can see, I'm in the process of migrating from mysql to
> > postgres. My next question to you guys is how does one implement
> > the mysql enum type. Also, when I issue the \dT command, I get
> > this nice list of data types. Are all those data types standard
> > SQL data types? or are some postgres "extensions"?
> >
> > Cheers. Steve.
Thanks Rod,
In otherwords, table myConstratins contains a list of string
values, ('blue','red','yellow','whatever'), and the
'reference "myConstratins" on update cascade' in the
constraintColumn definition tells postgres to make sure the
values put into that column must match one of the strings
listed in myConstraints. Correct? I also found the command
check (constraintColumn in ('blue','red','yellow','whatever'))
which I think I can replace 'reference "myConstraints" on update
cascade'. The difference between the two I assume is that the first
example, I can modify the values of the strings which can be
insterted into my column, while in the latter, the values of
the strings are basically fixed. Correct?
Where can I find info on all this? It does not seem to be covered
in the manuals distributed with postgresql.
Cheers. Steve.
On Sat, 2002-11-30 at 12:57, Rod Kreisler wrote:
> Define the constraint table:
>
> create table "myConstraints"
> (
> "myConstraint" varchar(32) not null primary key
> )
>
> in the referencing table declare the constrained column:
>
> "constraintColumn" varchar(32) not null references "myConstraints" on
> update cascade,
>
> Of course the size of the varchar is up to you, but should be the same in
> both tables. The "on update cascade" allows you to change the value of the
> "myConstraint" column in "myConstraints" and have it update the values for
> those matching entries in the "constraintColumn" in the referencing table.
>
> HTH
>
> Rod
>
> > -----Original Message-----
> > From: pgsql-novice-owner@postgresql.org
> > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Adler, Stephen
> > Sent: Saturday, November 30, 2002 12:49 PM
> > To: pgsql-novice@postgresql.org
> > Subject: Re: [NOVICE] enum
> >
> >
> > In replying to my own post, I realize that a mysql enum is
> > equal to a varchar with a constraint on the value of the
> > string inserted into the column, (which is what I want to do,
> > make sure only 2 or 3 different strings can stored in
> > the varchar column.) But I'm having a hard time finding
> > the syntax for the column constraint entry in the create
> > table command. anyone can let me know where I can find info
> > on the constraint syntax? I've looked through the postgresql
> > manuals and haven't found anything.
> >
> > Thanks again. Steve.
> >
> > On Sat, 2002-11-30 at 12:13, Adler, Stephen wrote:
> > > As you guys can see, I'm in the process of migrating from mysql to
> > > postgres. My next question to you guys is how does one implement
> > > the mysql enum type. Also, when I issue the \dT command, I get
> > > this nice list of data types. Are all those data types standard
> > > SQL data types? or are some postgres "extensions"?
> > >
> > > Cheers. Steve.
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Adler, Stephen
> Sent: Saturday, November 30, 2002 1:17 PM
> To: Rod Kreisler
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] enum
>
>
> Thanks Rod,
>
> In other words, table myConstratins contains a list of string
> values, ('blue','red','yellow','whatever'), and the
> 'reference "myConstratins" on update cascade' in the
> constraintColumn definition tells postgres to make sure the
> values put into that column must match one of the strings
> listed in myConstraints. Correct?
Yes.
> I also found the command
> check (constraintColumn in ('blue','red','yellow','whatever'))
> which I think I can replace 'reference "myConstraints" on update
> cascade'. The difference between the two I assume is that the first
> example, I can modify the values of the strings which can be
> insterted into my column, while in the latter, the values of
> the strings are basically fixed. Correct?
Yes. It's much harder to make changes to the list when you use the IN
constraint. Also, if you want to change 'yellow' to 'green' it takes many
more gyrations.
>
> Where can I find info on all this? It does not seem to be covered
> in the manuals distributed with postgresql.
It's under "SQL Syntax" - "Create Table"
http://www.postgresql.org/idocs/index.php?sql-createtable.html