Making a unique constraint deferrable?

From: Ivan Voras <ivoras(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Making a unique constraint deferrable?
Date: 2017-02-28 16:50:53
Message-ID: CAF-QHFVkmC7qsRSYdhk_p7SzqN235qrZ-C5q8aC5Ux0QSUrR2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

If I'm interpreting the manual correctly, this should work:

ivoras=# create table foo(a integer, b integer, unique(a,b));
CREATE TABLE
ivoras=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
Indexes:
"foo_a_b_key" UNIQUE CONSTRAINT, btree (a, b)

ivoras=# insert into foo(a,b) values(1,2);
INSERT 0 1
ivoras=# insert into foo(a,b) values(1,2);
ERROR: duplicate key value violates unique constraint "foo_a_b_key"
DETAIL: Key (a, b)=(1, 2) already exists.
ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
ERROR: constraint "foo_a_b_key" of relation "foo" is not a foreign key
constraint

The manual says this for SET CONSTRAINTS:

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE
constraints are affected by this setting. NOT NULL and CHECK constraints
are always checked immediately when a row is inserted or modified (not at
the end of the statement). Uniqueness and exclusion constraints that have
not been declared DEFERRABLE are also checked immediately.

I'm puzzled by the "...is not a foreign key constraint" error message.
Doesn't "deferrable" also work on unique constraints?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2017-02-28 16:58:48 Re: Making a unique constraint deferrable?
Previous Message David G. Johnston 2017-02-28 16:50:19 Re: ERROR: functions in index expression must be marked IMMUTABLE