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