Postgres 12

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 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE DOMAIN ADDR TEXT NOT NULL
CHECK (LENGTH(VALUE) >= 5 AND LENGTH(VALUE) <= 40);
CREATE TABLE test
(
  f_name TEXT NOT NULL,
  l_name TEXT NOT NULL,
  
  addr1 ADDR,            -- << MUCH tidier than TEXT NOT NULL CHECK (LENGTH... 
  addr2 ADDR,
  addr3 ADDR 
);
INSERT INTO test
(f_name, l_name, addr1, addr2)
VALUES
('prenom_1', 'nom_1', 'My_addr_1_1', 'My_addr_1_2'),
('prenom_2', 'nom_2', 'My_addr_2_2', 'My_addr_2_2'),
('prenom_3', 'nom_3', 'My_addr_3_1', 'My_addr_3_2');
ERROR:  domain addr does not allow null values


INSERT INTO test
(f_name, l_name, addr1, addr2, addr3)
VALUES
('prenom_1', 'nom_1', 'My_addr_1_1', 'My_addr_1_2', NULL),
('prenom_2', 'nom_2', 'My_addr_2_2', 'My_addr_2_2', NULL),
('prenom_3', 'nom_3', 'My_addr_3_1', 'My_addr_3_2', NULL);
ERROR:  domain addr does not allow null values


SELECT * FROM test;
INSERT INTO test
(f_name, l_name, addr1, addr2, addr3)
VALUES
('prenom_1', 'nom_1', 'My_addr_1_1', 'My_addr_1_2', 'My_addr_1_3'),
('prenom_2', 'nom_2', 'My_addr_2_2', 'My_addr_2_2', 'My_addr_2_3'),
('prenom_3', 'nom_3', 'My_addr_3_1', 'My_addr_3_2', 'My_addr_3_3');
3 rows affected

SELECT * FROM test;


f_name
l_name
addr1
addr2
addr3


prenom_1
nom_1
My_addr_1_1
My_addr_1_2
My_addr_1_3

prenom_2
nom_2
My_addr_2_2
My_addr_2_2
My_addr_2_3

prenom_3
nom_3
My_addr_3_1
My_addr_3_2
My_addr_3_3


UPDATE test SET addr3 = NULL WHERE f_name = 'prenom_3';
ERROR:  domain addr does not allow null values

f_name	l_name	addr1	addr2	addr3
prenom_1	nom_1	My_addr_1_1	My_addr_1_2	My_addr_1_3
prenom_2	nom_2	My_addr_2_2	My_addr_2_2	My_addr_2_3
prenom_3	nom_3	My_addr_3_1	My_addr_3_2	My_addr_3_3