Problem defining deferred check constraints

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem defining deferred check constraints
Date: 2009-01-25 13:34:33
Message-ID: glhpp7$qn6$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2009-01-25 13:37:29 Re: autovacuum daemon
Previous Message Jasen Betts 2009-01-25 02:21:15 Re: problem converting database to UTF-8