From: | Scott Rohde <srohde(at)illinois(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Check/unique constraint question |
Date: | 2014-12-09 18:01:39 |
Message-ID: | 1418148099196-5829778.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
There is something a bit odd about this solution: If you start with an empty
table, the constraint will allow you to do
INSERT INTO foo (active, id) VALUES ('t', 5);
But if you insert this row into the table first and /then/ try to add the
constraint, it will complain that an existing row violates the constraint.
This begs the question of when constraints are checked.
I had always thought of constraints as being static conditions that (unlike
some trigger condition that masquerades as a constraint) apply equally to
existing rows and to rows you are about to add. This seems to show that not
all constraints work this way.
Nikolay Samokhvalov wrote
> just a better way (workaround for subqueries in check constraints...):
>
> CREATE OR REPLACE FUNCTION id_is_valid(
> val INTEGER
> ) RETURNS boolean AS $BODY$
> BEGIN
> IF val IN (
> SELECT id FROM foo WHERE active = TRUE AND id = val
> ) THEN
> RETURN FALSE;
> ELSE
> RETURN TRUE;
> END IF;
> END
> $BODY$ LANGUAGE plpgsql;
> ALTER TABLE foo ADD CONSTRAINT C_foo_iniq_if_true CHECK (active =
> FALSE OR id_is_valid(id));
>
> ...
--
View this message in context: http://postgresql.nabble.com/Check-unique-constraint-question-tp2145289p5829778.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-12-09 18:43:15 | Re: Check/unique constraint question |
Previous Message | Tim Dudgeon | 2014-12-08 21:39:13 | Re: [SQL] querying with index on jsonb slower than standard column. Why? |