CODEWITHSUNDEEP
sqlpostgresqllinked-list
Anton
Anton

Reputation: 309

Iterate through singly-linked list in PostgreSQL

I have a table with a primary (unique) key id and a foreign key next_id which points to another (next) entry in this same table (or to none if it's the last entry).

So the example data may look like this:

| id        |    next_id |
| --------  | ---------- |
| 1         | 3          |
| 2         | null       |
| 3         | 2          |

What's the most effective SQL-query to iterate through such linked list in Posgres and return list of linked id's (would be [1, 3, 2] for the provided example table)?

Upvotes: 0

Views: 111

Answers (1)

Anton
Anton

Reputation: 309

I figured it out using the recursive query as suggested in comments by @Laurenz Albe

WITH recursive tmp_table as (
        SELECT id
        FROM main_table
        WHERE id = 1
    UNION
        SELECT m.id
        FROM main_table m
        INNER JOIN tmp_table t ON t.next_id = m.id
)

SELECT * FROM tmp_table;

Upvotes: 1

Related Questions