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 mytable (
name text,
attributes text[9]
);
INSERT INTO mytable VALUES
('Park 1', '{covered, handicap_access, elevator}'),
('Park 2', '{uncovered, always_open, handicap_access}');
SELECT * FROM mytable
2 rows affected
| name | attributes |
|---|---|
| Park 1 | {covered,handicap_access,elevator} |
| Park 2 | {uncovered,always_open,handicap_access} |
CREATE TABLE codes (
code int,
name text
);
INSERT INTO codes VALUES
(1, 'covered'),
(2, 'uncovered'),
(3, 'handicap_access'),
(4, 'XYZ'),
(5, 'always_open'),
(6, 'ABC'),
(7, 'elevator');
SELECT * FROM codes
7 rows affected
| code | name |
|---|---|
| 1 | covered |
| 2 | uncovered |
| 3 | handicap_access |
| 4 | XYZ |
| 5 | always_open |
| 6 | ABC |
| 7 | elevator |
SELECT
*
FROM mytable,
unnest(attributes) WITH ORDINALITY as elems(value, index)
| name | attributes | value | index |
|---|---|---|---|
| Park 1 | {covered,handicap_access,elevator} | covered | 1 |
| Park 1 | {covered,handicap_access,elevator} | handicap_access | 2 |
| Park 1 | {covered,handicap_access,elevator} | elevator | 3 |
| Park 2 | {uncovered,always_open,handicap_access} | uncovered | 1 |
| Park 2 | {uncovered,always_open,handicap_access} | always_open | 2 |
| Park 2 | {uncovered,always_open,handicap_access} | handicap_access | 3 |
SELECT
*
FROM mytable,
unnest(attributes) WITH ORDINALITY as elems(value, index)
JOIN codes c ON c.name = elems.value
| name | attributes | value | index | code | name |
|---|---|---|---|---|---|
| Park 2 | {uncovered,always_open,handicap_access} | always_open | 2 | 5 | always_open |
| Park 1 | {covered,handicap_access,elevator} | covered | 1 | 1 | covered |
| Park 1 | {covered,handicap_access,elevator} | elevator | 3 | 7 | elevator |
| Park 1 | {covered,handicap_access,elevator} | handicap_access | 2 | 3 | handicap_access |
| Park 2 | {uncovered,always_open,handicap_access} | handicap_access | 3 | 3 | handicap_access |
| Park 2 | {uncovered,always_open,handicap_access} | uncovered | 1 | 2 | uncovered |
SELECT
t.name,
array_agg(c.code ORDER BY elems.index) as codes
FROM mytable t,
unnest(attributes) WITH ORDINALITY as elems(value, index)
JOIN codes c ON c.name = elems.value
GROUP BY t.name
| name | codes |
|---|---|
| Park 1 | {1,3,7} |
| Park 2 | {2,5,3} |