I'm having trouble with a UNIQUE constraint that I need to have DEFERRABLE INITIALLY DEFERRED. On my Dev machine (Postgres 9.1) it works fine:
alter table foo drop constraint bar; ALTER TABLE
alter table foo add constraint bar UNIQUE (col1, col2) deferrable initially deferred; ALTER TABLE
In Production (Postgres 8.4.13) this blows up in my face although it should be supported according to the docs:
alter table foo drop constraint bar; ALTER TABLE
alter table foo add constraint bar UNIQUE (col1, col2) deferrable initially deferred; FEHLER: Syntaxfehler bei »DEFERRABLE« LINE 1: bar UNIQUE (col1, col2) DEFERRABLE... ^
(i.e. syntax error at "DEFERRABLE", psql seems to ignore my locale setting).
Does somebody know what the reason for this could be? I'm kind of puzzled.
My guess is you're looking in the wrong place in the docs - can you specify where you are looking? If you are in the right place then the docs are wrong.
Deferrable unique constraints are a new feature in PostgreSQL 9.0, so it shouldn't be working in 8.4.
(And as a sidenote, a reminder that 8.4 will go end of life in just a couple of months, so if you aren't already planning an upgrade of your production environment, it's probably a good idea to start doing that, see http://www.postgresql.org/support/versioning/)
