2009/8/11 hubert depesz lubaczewski <dep...@depesz.com>: > While testing deferred unique constraints I found this: > > # CREATE TABLE test ( > i INT4 PRIMARY KEY > ); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" > for table "test" > CREATE TABLE > > # set constraints test_pkey deferred; > ERROR: constraint "test_pkey" does not exist >
The constraint needs to be declared DEFERRABLE before you can defer it, but yes, I agree this is not a helpful error message. [The reason is that it actually searches for the trigger enforcing the constraint, and there isn't one if it's not deferrable. So the current code can't distinguish between a non-existent unique constraint and a non-deferrable one.] > As I understand (I might be wrong, so please clarify if I am), when I > create table with primary key that is "deferrable initially immediate", > it will act as immediate unless i will set it to deferred with "set > constraints". > > If that's true, then why it works: > # INSERT INTO test (i) values (1), (2), (3); > INSERT 0 3 > # update test set i = i + 1; > UPDATE 3 > > shouldn't it raise exception? and work *only* if i set the constraint to > deferred? > "Immediate" actually means at the end of the statement rather than after each row for deferrable constraints. See http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html - Dean -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
