Re: CHECK Constraint Deferrable - Mailing list pgsql-hackers

3) Insert check is not deferred to commit:
This insert check here is deferred to commit:
postgres=# CREATE TABLE tbl (i int ) partition by range (i);
CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES FROM (0) TO (10);
CREATE TABLE tbl_2 PARTITION OF tbl FOR VALUES FROM (20) TO (30);
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=# ALTER TABLE tbl ADD CONSTRAINT tbl_chk_1 CHECK(i<>1) DEFERRABLE;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=*# SET CONSTRAINTS tbl_chk_1 DEFERRED;
SET CONSTRAINTS
postgres=*# INSERT INTO tbl values (1);
INSERT 0 1
postgres=*# commit;
ERROR:  new row for relation "tbl_1" violates check constraint "tbl_chk_1"
DETAIL:  Failing row contains (1).

But the check here is not deferred to commit:
postgres=# CREATE TABLE tbl (i int check(i<>0) DEFERRABLE) partition
by range (i);
CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES FROM (0) TO (10);
CREATE TABLE tbl_2 PARTITION OF tbl FOR VALUES FROM (20) TO (30);
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=# ALTER TABLE tbl ADD CONSTRAINT tbl_chk_1 CHECK(i<>1) DEFERRABLE;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=*#  SET CONSTRAINTS tbl_chk_1 DEFERRED;
SET CONSTRAINTS
postgres=*# INSERT INTO tbl values (1);
ERROR:  new row for relation "tbl_1" violates check constraint "tbl_chk_1"
DETAIL:  Failing row contains (1).

‘postgres[1271421]=#’CREATE TABLE tbl (i int check(i<>0) DEFERRABLE) partition
‘...>’by range (i);
CREATE TABLE
‘postgres[1271421]=#’CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES FROM (0) TO (10);
CREATE TABLE
‘postgres[1271421]=#’CREATE TABLE tbl_2 PARTITION OF tbl FOR VALUES FROM (20) TO (30);
CREATE TABLE
‘postgres[1271421]=#’ALTER TABLE tbl ADD CONSTRAINT tbl_chk_1 CHECK(i<>1) DEFERRABLE;
ALTER TABLE
‘postgres[1271421]=#’\d tbl
          Partitioned table "public.tbl"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 i      | integer |           |          |
Partition key: RANGE (i)
Check constraints:
    "tbl_chk_1" CHECK (i <> 1) DEFERRABLE
    "tbl_i_check" CHECK (i <> 0) DEFERRABLE
Number of partitions: 2 (Use \d+ to list them.)
BEGIN
‘postgres[1271421]=#*’SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
‘postgres[1271421]=#*’INSERT INTO tbl values (1);
INSERT 0 1
‘postgres[1271421]=#*’commit;
ERROR:  23514: new row for relation "tbl_1" violates check constraint "tbl_chk_1"
DETAIL:  Failing row contains (1).
SCHEMA NAME:  public
TABLE NAME:  tbl_1
CONSTRAINT NAME:  tbl_chk_1
LOCATION:  ExecConstraints, execMain.c:2077