Thread: CHECK Constraints
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) .
On Fri, Apr 22, 2005 at 12:02:41 -0400, "Van Ingen, Lane" <lvaningen@ESNCC.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.
On Fri, Apr 22, 2005 at 12:02:41PM -0400, Van Ingen, Lane wrote:
>
> 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)
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/
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@fuhr.org]
Sent: Fri 4/22/2005 12:19 PM
To: Van Ingen, Lane
Cc: pgsql-novice@postgresql.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@wolff.to]
Sent: Fri 4/22/2005 12:37 PM
To: Van Ingen, Lane
Cc: pgsql-novice@postgresql.org
Subject: Re: CHECK Constraints
On Fri, Apr 22, 2005 at 12:02:41 -0400,
"Van Ingen, Lane" <lvaningen@ESNCC.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.