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

Re: CHECK Constraints

From: "Van Ingen, Lane" <lvaningen(at)ESNCC(dot)com>
To: "Bruno Wolff III" <bruno(at)wolff(dot)to>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: CHECK Constraints
Date: 2005-04-22 16:59:55
Message-ID: A3FF4275060B76459B5C08A64AE330C805C2EE@twmail.esncc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks for the help. I just tried it, and it works.
 
For readers: this seems to work well as a substitute for the MySQL datatype 'enum'. We 
are converting to PostgreSQL from MySQL. Looks like CHECK CONSTRAINT should 
handle the MySQL enum feature quite nicely. 
 
Just thought I would post the usage in case someone else is looking for a similar solution.
 
-----Original Message----- 
From: Michael Fuhr [mailto:mike(at)fuhr(dot)org] 
Sent: Fri 4/22/2005 12:19 PM 
To: Van Ingen, Lane 
Cc: pgsql-novice(at)postgresql(dot)org 
Subject: Re: [NOVICE] CHECK Constraints

What happened when you tried it?  A simple test should answer the
question.

CREATE TABLE foo (
    id     serial PRIMARY KEY,
    price  integer NOT NULL CHECK (price = 1 OR price = 2 OR price = 3)
);

INSERT INTO foo (price) VALUES (1);
INSERT 0 1

INSERT INTO foo (price) VALUES (2);
INSERT 0 1

INSERT INTO foo (price) VALUES (3);
INSERT 0 1

INSERT INTO foo (price) VALUES (0);
ERROR:  new row for relation "foo" violates check constraint "foo_price_check"

INSERT INTO foo (price) VALUES (4);
ERROR:  new row for relation "foo" violates check constraint "foo_price_check"

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

-----Original Message----- 
From: Bruno Wolff III [mailto:bruno(at)wolff(dot)to] 
Sent: Fri 4/22/2005 12:37 PM 
To: Van Ingen, Lane 
Cc: pgsql-novice(at)postgresql(dot)org 
Subject: Re: CHECK Constraints

On Fri, Apr 22, 2005 at 12:02:41 -0400,
  "Van Ingen, Lane" <lvaningen(at)ESNCC(dot)com> wrote:
> Hi all,
>   the 8.0 manual says CHECK can be used in the following manner:
>        CHECK (expression)
> The only stipulations are that it must produce a boolean result (true or unknown) to succeed.
> 
> If I were to specify a the following on a field named price in one expression, would it succeed?
>      CHECK (price = 1 OR price = 2 OR price = 3)
> 
> In other words, I would want to raise an exception on any other value other than 1, 2, or 3. Can
> this be done? All of the examples in the manual show a single check (like price > 0) .

Yes, you can do that.

pgsql-novice by date

Next:From: Keith WorthingtonDate: 2005-04-22 19:29:33
Subject: CHECK vs BEFORE trigger
Previous:From: Walker, Jed SDate: 2005-04-22 16:47:06
Subject: Re: Granting permission on a sequence to a group

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