Re: Check/unique constraint question

Поиск
Список
Период
Сортировка
От Scott Rohde
Тема Re: Check/unique constraint question
Дата
Msg-id 1418148099196-5829778.post@n5.nabble.com
обсуждение исходный текст
Ответ на Re: Check/unique constraint question  ("Nikolay Samokhvalov" <samokhvalov@gmail.com>)
Ответы Re: Check/unique constraint question  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.



В списке pgsql-sql по дате отправления:

Предыдущее
От: Tim Dudgeon
Дата:
Сообщение: Re: querying with index on jsonb slower than standard column. Why?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Check/unique constraint question