Postgres 9.5

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 9.5.24 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


SELECT addr1 FROM test WHERE false;
INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'a',
   'b',
   (SELECT addr1 FROM test WHERE false),
   (SELECT addr1 FROM test WHERE false),
   (SELECT addr1 FROM test WHERE false)
  ) ;
1 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

a
b
null
null
null


INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'c',
   'd',
   (SELECT addr1::ADDR FROM test WHERE false),
   (SELECT addr1::ADDR FROM test WHERE false),
   (SELECT addr1::ADDR FROM test WHERE false)
  ) ;
1 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

a
b
null
null
null

c
d
null
null
null


INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'e',
   'f',
   (SELECT addr1 FROM test WHERE 1 = 0),
   (SELECT addr1 FROM test WHERE 1 = 0),
   (SELECT addr1 FROM test WHERE 1 = 0)
  ) ;
1 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

a
b
null
null
null

c
d
null
null
null

e
f
null
null
null


DROP TABLE test;
CREATE TABLE test
(
  f_name TEXT NOT NULL,
  l_name TEXT NOT NULL,
  
  addr1 TEXT NOT NULL CHECK (LENGTH(addr1) >= 5 AND LENGTH(addr1) <= 40),            -- << MUCH tidier than TEXT NOT NULL CHECK (LENGTH... 
  addr2 TEXT NOT NULL CHECK (LENGTH(addr1) >= 5 AND LENGTH(addr1) <= 40),
  addr3 TEXT NOT NULL CHECK (LENGTH(addr1) >= 5 AND LENGTH(addr1) <= 40)
);
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


INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'a',
   'b',
   (SELECT addr1 FROM test WHERE false),
   (SELECT addr1 FROM test WHERE false),
   (SELECT addr1 FROM test WHERE false)
  ) ;
ERROR:  null value in column "addr1" violates not-null constraint
DETAIL:  Failing row contains (a, b, null, null, null).


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
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