Hi,
Currently, there is no support for CHECK constraint DEFERRABLE in a create table statement.
SQL standard specifies that CHECK constraint can be defined as DEFERRABLE.
The attached patch is having implementation for CHECK constraint Deferrable as below:
‘postgres[579453]=#’CREATE TABLE t1 (i int CHECK(i<>0) DEFERRABLE, t text);
CREATE TABLE
‘postgres[579453]=#’\d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
t | text | | |
Check constraints:
"t1_i_check" CHECK (i <> 0) DEFERRABLE
Now we can have a deferrable CHECK constraint, and we can defer the constraint validation:
‘postgres[579453]=#’BEGIN;
BEGIN
‘postgres[579453]=#*’SET CONSTRAINTS t1_i_check DEFERRED;
SET CONSTRAINTS
‘postgres[579453]=#*’INSERT INTO t1 VALUES (0, 'one'); -- should succeed
INSERT 0 1
‘postgres[579453]=#*’UPDATE t1 SET i = 1 WHERE t = 'one';
UPDATE 1
‘postgres[579453]=#*’COMMIT; -- should succeed
COMMIT
Attaching the initial patch, I will improve it with documentation in my next version of the patch.
thoughts?
--