On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivo...@gmail.com> wrote:
> 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? > > The error is pointing out the documented behavior that only FK constraints can be altered. https://www.postgresql.org/docs/9.6/static/sql-altertable.html (see ALTER CONSTRAINT note) So, while you can make a PK constraint deferrable it must be done as part of the initial constraint construction and not via ALTER CONSTRAINT. David J.
