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 types(type_key int primary key, name varchar, default_value varchar);
insert into types select 1,'DOB','1/1/1950';
insert into types select 2,'First_name','Sample';
insert into types select 3,'Last_name','Sample1';
insert into types select 4,'Contact','888888';

create table attributes(obj_key int not null, type_key int not null,status varchar not null,
v_date timestamp[], v_text text[], 
v_number int[], updated_on timestamp,
FOREIGN KEY (type_key) REFERENCES types (type_key));

insert into attributes select 1, 1, 'active', '{1987-06-04}', null, null,'{2021-06-04}';
insert into attributes select 1, 2, 'active', null, array['GB'], null,'{2021-05-04}';
insert into attributes select 1, 3, 'active', null, null, null,'{2021-06-07}';
insert into attributes select 1, 4, 'active', null, null, null,'{2021-06-04}';

insert into attributes select 2, 1, 'active', '{1988-06-04}', null, null,'{2020-06-07}';
insert into attributes select 2, 2, 'active', null, array['Nick'], null,'{2020-05-04}';
insert into attributes select 2, 3, 'deleted', null, array['Jerry'], null,'{2019-06-04}';
insert into attributes select 2, 4, 'active', null, null, array[569251],'{2020-06-04}';

insert into attributes select 3, 2, 'active', null, array['Goerge'], null,'{2021-01-04}';
insert into attributes select 3, 3, 'active', null, array['M'], null,'{2020-06-04}';

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from types;


type_key
name
default_value


1
DOB
1/1/1950

2
First_name
Sample

3
Last_name
Sample1

4
Contact
888888


select * from attributes;


obj_key
type_key
status
v_date
v_text
v_number
updated_on


1
1
active
{"1987-06-04 00:00:00"}
null
null
2021-06-04 00:00:00

1
2
active
null
{GB}
null
2021-05-04 00:00:00

1
3
active
null
null
null
2021-06-07 00:00:00

1
4
active
null
null
null
2021-06-04 00:00:00

2
1
active
{"1988-06-04 00:00:00"}
null
null
2020-06-07 00:00:00

2
2
active
null
{Nick}
null
2020-05-04 00:00:00

2
3
deleted
null
{Jerry}
null
2019-06-04 00:00:00

2
4
active
null
null
{569251}
2020-06-04 00:00:00

3
2
active
null
{Goerge}
null
2021-01-04 00:00:00

3
3
active
null
{M}
null
2020-06-04 00:00:00


select obj_key, max(updated_on) as updated_on, max(status) as status, 
      max(array_to_string(v_date,'||'::text)) filter (where type_key=1) as "DOB",
      max(array_to_string(v_text,'||'::text)) filter (where type_key=2) as "First Name",
      max(array_to_string(v_text,'||'::text)) filter (where type_key=3) as "Last Name",
      max(array_to_string(v_number,'||'::text)) filter (where type_key=4) as "Contact"
from attributes group by obj_key 


obj_key
updated_on
status
DOB
First Name
Last Name
Contact


3
2021-01-04 00:00:00
active
null
Goerge
M
null

2
2020-06-07 00:00:00
deleted
1988-06-04 00:00:00
Nick
Jerry
569251

1
2021-06-07 00:00:00
active
1987-06-04 00:00:00
GB
null
null


select obj_key, max(updated_on) as updated_on, max(att.status) as status,
  COALESCE(max(array_to_string(v_date,'||'::text)) filter (where att.type_key=1),(select default_value from types where type_key=1))  as "DOB",
  COALESCE(max(array_to_string(v_text,'||'::text)) filter (where att.type_key=2),(select default_value from types where type_key=2)) as "First Name",
  COALESCE(max(array_to_string(v_text,'||'::text)) filter (where att.type_key=3),(select default_value from types where type_key=3))as "Last Name",
  COALESCE(max(array_to_string(v_number,'||'::text)) filter (where att.type_key=4),(select default_value from types where type_key=4)) as "Contact"
  from attributes att right join types ty on att.type_key=ty.type_key
  group by obj_key 


obj_key
updated_on
status
DOB
First Name
Last Name
Contact


3
2021-01-04 00:00:00
active
1/1/1950
Goerge
M
888888

2
2020-06-07 00:00:00
deleted
1988-06-04 00:00:00
Nick
Jerry
569251

1
2021-06-07 00:00:00
active
1987-06-04 00:00:00
GB
Sample1
888888


select DISTINCT ON (obj_key) 
       obj_key, updated_on, 
   first_value(status) OVER w AS created_by,
  COALESCE(max(array_to_string(v_date,'||'::text)) filter (where att.type_key=1),(select default_value from types where type_key=1)) OVER w as "DOB",
  COALESCE(max(array_to_string(v_text,'||'::text)) filter (where att.type_key=2),(select default_value from types where type_key=2)) OVER w as "First Name",
  COALESCE(max(array_to_string(v_text,'||'::text)) filter (where att.type_key=3),(select default_value from types where type_key=3)) OVER w as "Last Name",
  COALESCE(max(array_to_string(v_number,'||'::text)) filter (where att.type_key=4),(select default_value from types where type_key=4)) OVER w as "Contact"
  from attributes att right join types ty on att.type_key=ty.type_key
WINDOW w AS (PARTITION BY obj_key ORDER BY updated_on)
ORDER  BY obj_key, updated_on DESC NULLS LAST;
ERROR:  syntax error at or near "OVER"
LINE 4: ...elect default_value from types where type_key=1)) OVER w as ...
                                                             ^

type_key	name	default_value
1	DOB	1/1/1950
2	First_name	Sample
3	Last_name	Sample1
4	Contact	888888

obj_key	type_key	status	v_date	v_text	v_number	updated_on
1	1	active	{"1987-06-04 00:00:00"}	null	null	2021-06-04 00:00:00
1	2	active	null	{GB}	null	2021-05-04 00:00:00
1	3	active	null	null	null	2021-06-07 00:00:00
1	4	active	null	null	null	2021-06-04 00:00:00
2	1	active	{"1988-06-04 00:00:00"}	null	null	2020-06-07 00:00:00
2	2	active	null	{Nick}	null	2020-05-04 00:00:00
2	3	deleted	null	{Jerry}	null	2019-06-04 00:00:00
2	4	active	null	null	{569251}	2020-06-04 00:00:00
3	2	active	null	{Goerge}	null	2021-01-04 00:00:00
3	3	active	null	{M}	null	2020-06-04 00:00:00