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 chain_data
(
id integer not null,
unique_identifiers_id integer not null,
chain_data_name varchar(255) not null,
carriage boolean default false,
left_id integer ,
right_id integer
);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (10, 8, 'dddd_2', true, 22, null);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (22, 8, 'shuba', false, 23, 10);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (24, 8, 'viktor', false, null, 23);
INSERT INTO chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) VALUES (23, 8, 'ivan', false, 24, 22);
SELECT * FROM chain_data
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
| id | unique_identifiers_id | chain_data_name | carriage | left_id | right_id |
|---|---|---|---|---|---|
| 10 | 8 | dddd_2 | t | 22 | null |
| 22 | 8 | shuba | f | 23 | 10 |
| 24 | 8 | viktor | f | null | 23 |
| 23 | 8 | ivan | f | 24 | 22 |
SELECT 4
WITH RECURSIVE chain AS (
SELECT id, right_id
FROM chain_data
WHERE left_id IS NULL
UNION
SELECT cd.id, cd.right_id
FROM chain_data cd
JOIN chain c ON c.right_id = cd.id
)
SELECT
*
FROM
chain
| id | right_id |
|---|---|
| 24 | 23 |
| 23 | 22 |
| 22 | 10 |
| 10 | null |
SELECT 4
WITH RECURSIVE chain AS (
SELECT id, right_id
FROM chain_data
WHERE left_id IS NULL
UNION
SELECT cd.id, cd.right_id
FROM chain_data cd
JOIN chain c ON c.right_id = cd.id
)
SELECT
string_agg(id::text, ', ')
FROM
chain
| string_agg |
|---|
| 24, 23, 22, 10 |
SELECT 1