Hi, all.
I have a wonder about the behaviour of creating table which has a constraint
contradiction.
I created below table.
bugtest=# create table ct (a int, CHECK(a is not null and a >= 0 and a < 100 and a >= 200 and a < 300));
bugtest=# \d+ ct
Table "public.ct"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
Check constraints:
"ct_a_check" CHECK (a IS NOT NULL AND a >= 0 AND a < 100 AND a >= 200 AND a < 300)
Are there any rows which can satisfy the ct's CHECK constraint? If not, why we
allow creating table when check constraint itself is contradicted?
I originally noticed this while creating partitioned range table as below.
bugtest=# create table rt (a int) partition by range (a);
bugtest=# create table rt_sub1 partition of rt for values from (0) to (100) partition by range (a);
bugtest=# create table rt_sub2 partition of rt for values from (100) to (200) partition by range (a);
bugtest=# create table rt150 partition of rt_sub1 for values from (150) to (151);
bugtest=# \d+ rt_sub1
Table "public.rt_sub1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
Partition of: rt FOR VALUES FROM (0) TO (100)
Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 100))
Partition key: RANGE (a)
Partitions: rt150 FOR VALUES FROM (150) TO (151)
bugtest=# \d+ rt150
Table "public.rt150"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
Partition of: rt_sub1 FOR VALUES FROM (150) TO (151)
Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 100) AND (a IS NOT NULL) AND (a >= 150) AND (a < 151))
Any rows are not routed to rt150 through rt nor we can't insert any rows to
rt150 directly because of its constraints. If we add check whether constraint
is contradicted, it prevent us from accidentally creating useless table like
above rt150 which would not contain any rows.
I thought there might be a discussion or documentation about this, but I
couldn't find it. If there is, please also tell me that.
Thanks,
--
Yoshikazu Imai
