Resp.: Problem defining deferred check constraints

2009/1/25, Thomas Kellerer <spam_eater(at)gmx(dot)net>:
> Hi,
>
> I'm playing around with deferred constraints and according to the manual, it
> should be possible to declare a check constraint as deferred.
>
> At least that's how I read the definition of /column_constraint/ at
> http://www.postgresql.org/docs/8.3/static/sql-createtable.html
>
> What I tried:
>
> CREATE TABLE my_check_test
> (
> some_value integer
> constraint value_check
> check (some_value > 0) DEFERRABLE INITIALLY DEFERRED
> );
>
> That gives me the following error:
>
> ERROR: misplaced DEFERRABLE clause
>
> (when I remove "DEFERRABLE INITIALLY DEFERRED", it works)
>
> OK, so I tried to define the constraint at the end of the table definition:
>
> CREATE TABLE my_check_test
> (
> some_value integer,
>
> CONSTRAINT value_check
> CHECK (some_value > 0) DEFERRABLE INITIALLY DEFERRED
> );
>
> (Note the comma after the column definition)
>
> That gives me: ERROR: syntax error at or near "DEFERRABLE"
>
> OK, say maybe it's not possible with an "inline" constraint, so I tried to
> add
> the constraint after creating the table:
>
> ALTER TABLE my_check_test
> ADD CONSTRAINT value_check
> CHECK (some_value > 0) DEFERRABLE INITIALLY DEFERRED
> ;
>
> gives me: ERROR: syntax error at or near "DEFERRABLE"
>
> The above (ADD CONSTRAINT) syntax works when using it for a foreign key (so
> the
> keywords for deferring the constraint are at the right place)
>
>
> I'm sure I'm missing something very obvious, but what? :)
>

See the manual:
http://www.postgresql.org/docs/current/interactive/sql-createtable.html
"DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint
that is not deferrable will be checked immediately after every
command. Checking of constraints that are deferrable can be postponed
until the end of the transaction (using the SET CONSTRAINTS command).
NOT DEFERRABLE is the default. *Only foreign key constraints currently
accept this clause. All other constraint types are not deferrable.*"

Osvaldo