Problem defining deferred check constraints

2009/1/25, Thomas Kellerer <spam_eater@gmx.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

Osvaldo Kussama wrote on 25.01.2009 15:24:

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.*"

Thanks for reading the manual for me :)

Maybe the syntax definition for the /column_constraint/ part should be reworked
to make that clearer, by e.g. putting the DEFERRED stuff in to the "choice" for
FK definitions not at the end where it is "legal" for any constraint.

Regards
Thomas

On Sun, 25 Jan 2009, Thomas Kellerer wrote:

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

In the full description in that page for deferrable/not deferrable, it
also states: "Only foreign key constraints currently accept this clause."

Currently, you'd probably need to use a constraint trigger to check the
constraint to get similar functionality.