Thread: Problem defining deferred check constraints
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? :) Thanks for any input Thomas
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.