Postgres 10

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

create table old (companyID int, updated_at date, updates varchar(100));
insert into old values (101, '2020-11-01', E'name:\n-ABC\n-XYZ\nurl:\n-www.abc.com\n-www.xyz.com');
insert into old values (109, '2020-10-20', E'rating:\n-4.5\n-4.0');
1 rows affected

1 rows affected

create table intermediate (companyID int, updated_at date, updates varchar(100));
insert into intermediate select companyID, updated_at, regexp_split_to_table(updates, '\n(?=\y.+:)') as updates from old;
3 rows affected

select * from intermediate;


companyid
updated_at
updates


101
2020-11-01
name:
-ABC
-XYZ

101
2020-11-01
url:
-www.abc.com
-www.xyz.com

109
2020-10-20
rating:
-4.5
-4.0


select companyID, updated_at, split_part(updates, E':', 1) as field, split_part(updates, E'\n-', 2) as old_value, split_part(updates, E'\n-', 3) as new_value  from intermediate;


companyid
updated_at
field
old_value
new_value


101
2020-11-01
name
ABC
XYZ

101
2020-11-01
url
www.abc.com
www.xyz.com

109
2020-10-20
rating
4.5
4.0

companyid	updated_at	updates
101	2020-11-01	name: -ABC -XYZ
101	2020-11-01	url: -www.abc.com -www.xyz.com
109	2020-10-20	rating: -4.5 -4.0

companyid	updated_at	field	old_value	new_value
101	2020-11-01	name	ABC	XYZ
101	2020-11-01	url	www.abc.com	www.xyz.com
109	2020-10-20	rating	4.5	4.0