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, array['Steve'], null,'{2021-06-07}';
insert into attributes select 1, 4, 'active', null, null, array[823532],'{2021-06-04}';
insert into attributes select 2, 1, 'active', '{1988-06-04}', null, null,'{2021-06-07}';
insert into attributes select 2, 2, 'active', null, array['Nick'], null,'{2021-05-04}';
insert into attributes select 2, 3, 'active', null, array['Jerry'], null,'{2021-06-04}';
insert into attributes select 2, 4, 'active', null, null, array[569251],'{2021-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}';
insert into attributes select 3, 4, 'active', null, null, array[458201],'{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
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 | {Steve} | null | 2021-06-07 00:00:00 |
| 1 | 4 | active | null | null | {823532} | 2021-06-04 00:00:00 |
| 2 | 1 | active | {"1988-06-04 00:00:00"} | null | null | 2021-06-07 00:00:00 |
| 2 | 2 | active | null | {Nick} | null | 2021-05-04 00:00:00 |
| 2 | 3 | active | null | {Jerry} | null | 2021-06-04 00:00:00 |
| 2 | 4 | active | null | null | {569251} | 2021-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 |
| 3 | 4 | active | null | null | {458201} | 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 | 458201 |
| 2 | 2021-06-07 00:00:00 | active | 1988-06-04 00:00:00 | Nick | Jerry | 569251 |
| 1 | 2021-06-07 00:00:00 | active | 1987-06-04 00:00:00 | GB | Steve | 823532 |
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),max(default_value)) as "DOB",
max(array_to_string(v_text,'||'::text)) filter (where att.type_key=2) as "First Name",
max(array_to_string(v_text,'||'::text)) filter (where att.type_key=3) as "Last Name",
max(array_to_string(v_number,'||'::text)) filter (where att.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 | Sample1 | Goerge | M | 458201 |
| 2 | 2021-06-07 00:00:00 | active | 1988-06-04 00:00:00 | Nick | Jerry | 569251 |
| 1 | 2021-06-07 00:00:00 | active | 1987-06-04 00:00:00 | GB | Steve | 823532 |