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 APPLE
( APPLE_ID INTEGER,
PHONE_ID INTEGER,
IPAD_ID INTEGER);
INSERT INTO APPLE VALUES (1,1001,2001);
INSERT INTO APPLE VALUES (2,1002,2002);
INSERT INTO APPLE VALUES (3,1003,2003);
INSERT INTO APPLE VALUES (4,1004,2004);
CREATE TABLE KEEY
( KEEY_ID INTEGER,
NAME VARCHAR(50),
DTL_ID INTEGER,
DEVICE_ID INTEGER);
INSERT INTO KEEY VALUES (1,'PHONE_ID',1001,111);
INSERT INTO KEEY VALUES (2,'PHONE_ID',2001,111);
INSERT INTO KEEY VALUES (3,'IPAD_ID',2001,222);
INSERT INTO KEEY VALUES (4,'PHONE_ID',1003,444);
INSERT INTO KEEY VALUES (5,'MAC_ID',367,333);
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 a.*, k1.device_id as phone_id, k2.device_id as ipad_id
from apple a join
keey k1
on a.phone_id = k1.dtl_id and k1.name = 'PHONE_ID' left join
keey k2
on a.ipad_id = k2.dtl_id and k2.name = 'IPAD_ID'
| apple_id | phone_id | ipad_id | phone_id | ipad_id |
|---|---|---|---|---|
| 1 | 1001 | 2001 | 111 | 222 |
| 3 | 1003 | 2003 | 444 | null |