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

Re: Enumerated (enum) types

From: Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Enumerated (enum) types
Date: 2008-05-27 16:08:13
Message-ID: pud4n7raqa.fsf@srv.protecting.net (view raw or flat)
Thread:
Lists: pgsql-sql
In article <5ac667b80805270729x4f93cc14n4a868d3d3f624d7b(at)mail(dot)gmail(dot)com>,
"Michael Lourant" <lourant(at)gmail(dot)com> writes:

> Type Safety

> Enumerated types are completely separate data types and may not be compared
> with each other.

...

> An Alternative Way To Do The Same

> Instead of using an enum type we can set up a CHECK CONSTRAINT - this tells
> postgresql to make sure that the value we are entering is valid.

> CREATE TABLE person (
>  personid int not null primary key,
>  favourite_colour varchar(255) NOT NULL,
>  CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))

> );

> INSERT INTO person(personid, favourite_colour) VALUES (1, 'red');
> INSERT 0 1

> Now for something not in the list:

> INSERT INTO person(personid, favourite_colour) VALUES (2, 'green');
> ERROR: new row for relation "person" violates check constraint "person_favourite_colour_check"

Type safety is the thing you lose by replacing an ENUM by a CHECK
constraint - you can still do something nonsensical like

SELECT * FROM person WHERE favourite_colour = 'green'


In response to

pgsql-sql by date

Next:From: Richard BroersmaDate: 2008-05-27 16:29:56
Subject: Re: Extremely Low performance with ODBC
Previous:From: Tarlika Elisabeth SchmitzDate: 2008-05-27 15:53:05
Subject: UPDATE with table join

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