Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE emp (test_check int check ( test_check >1 and test_check < 0 ));
select * from pg_constraint where conname = 'emp_test_check_check';


oid
conname
connamespace
contype
condeferrable
condeferred
convalidated
conrelid
contypid
conindid
conparentid
confrelid
confupdtype
confdeltype
confmatchtype
conislocal
coninhcount
connoinherit
conkey
confkey
conpfeqop
conppeqop
conffeqop
conexclop
conbin


189619
emp_test_check_check
2200
c
f
f
t
189616
0
0
0
0
 
 
 
t
0
f
{1}
null
null
null
null
null
{BOOLEXPR :boolop and :args ({OPEXPR :opno 521 :opfuncid 147 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 41} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 53 :constvalue 4 [ 1 0 0 0 0 0 0 0 ]}) :location 52} {OPEXPR :opno 97 :opfuncid 66 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 59} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 72 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]}) :location 70}) :location 55}


select pgc.conname as constraint_name,
       ccu.table_schema as table_schema,
       ccu.table_name,
       ccu.column_name,
       pg_get_constraintdef(pgc.oid) 
from pg_constraint pgc
join pg_namespace nsp on nsp.oid = pgc.connamespace
join pg_class  cls on pgc.conrelid = cls.oid
left join information_schema.constraint_column_usage ccu
          on pgc.conname = ccu.constraint_name
          and nsp.nspname = ccu.constraint_schema
where contype ='c'
order by pgc.conname;


constraint_name
table_schema
table_name
column_name
pg_get_constraintdef


emp_test_check_check
public
emp
test_check
CHECK (((test_check > 1) AND (test_check < 0)))


alter table emp  VALIDATE CONSTRAINT emp_test_check_check;
select * from pg_constraint where conname = 'emp_test_check_check';


oid
conname
connamespace
contype
condeferrable
condeferred
convalidated
conrelid
contypid
conindid
conparentid
confrelid
confupdtype
confdeltype
confmatchtype
conislocal
coninhcount
connoinherit
conkey
confkey
conpfeqop
conppeqop
conffeqop
conexclop
conbin


189619
emp_test_check_check
2200
c
f
f
t
189616
0
0
0
0
 
 
 
t
0
f
{1}
null
null
null
null
null
{BOOLEXPR :boolop and :args ({OPEXPR :opno 521 :opfuncid 147 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 41} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 53 :constvalue 4 [ 1 0 0 0 0 0 0 0 ]}) :location 52} {OPEXPR :opno 97 :opfuncid 66 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 59} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 72 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]}) :location 70}) :location 55}