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


CREATE TABLE tab
(
  id INT, user_id INT, title VARCHAR (20), data VARCHAR (20)
);
INSERT INTO tab VALUES
(1,	1,	'DATE',   '01-01-2021'), 
(2,	1,	'COLOR',  'RED'),
(3,	2,	'DATE',	   NULL),
(4,	2,	'COLOR',  'YELLOW'),
(5,	3,	'DATE',	  '03-03-2023'),
(6,	3,	'COLOR',  'RED'),
(5,	4,	'DATE',	  '04-04-2024'),
(6,	4,	'COLOR',  'PINK'),
(7,	5,	'DATE',   '05-05-2025'),
(8,	5,	'COLOR',  'PINK'),
(7,	6,	'DATE',	  '06-06-2026'),
(8,	6,	'COLOR',  'PINK'),
(3,	7,	'DATE',	   '07-07-2027'),
(4,	7,	'COLOR',  'YELLOW');
14 rows affected

--
-- Try this on PostgreSQL - a decent RDBMS!
--
SELECT *, 
  REGEXP_REPLACE(data, '(\d\d)-(\d\d)-(\d\d\d\d)', '\3-\2-\1') AS d1,
  REGEXP_REPLACE(data, '(^.*)-(\d\d)-(.*$)', '\3-\2-\1') AS d2,
  REGEXP_REPLACE(data, '(^\d{2})-(\d{2})-(\d{4})', '\3-\2-\1') AS d3
FROM tab WHERE title = 'DATE'
ORDER BY user_id;


id
user_id
title
data
d1
d2
d3


1
1
DATE
01-01-2021
2021-01-01
2021-01-01
2021-01-01

3
2
DATE
null
null
null
null

5
3
DATE
03-03-2023
2023-03-03
2023-03-03
2023-03-03

5
4
DATE
04-04-2024
2024-04-04
2024-04-04
2024-04-04

7
5
DATE
05-05-2025
2025-05-05
2025-05-05
2025-05-05

7
6
DATE
06-06-2026
2026-06-06
2026-06-06
2026-06-06

3
7
DATE
07-07-2027
2027-07-07
2027-07-07
2027-07-07

id	user_id	title	data	d1	d2	d3
1	1	DATE	01-01-2021	2021-01-01	2021-01-01	2021-01-01
3	2	DATE	null	null	null	null
5	3	DATE	03-03-2023	2023-03-03	2023-03-03	2023-03-03
5	4	DATE	04-04-2024	2024-04-04	2024-04-04	2024-04-04
7	5	DATE	05-05-2025	2025-05-05	2025-05-05	2025-05-05
7	6	DATE	06-06-2026	2026-06-06	2026-06-06	2026-06-06
3	7	DATE	07-07-2027	2027-07-07	2027-07-07	2027-07-07