PostgreSQL nested suqueries

Question

Suppose I have following tables in PostgreSQL database:

base_rel (id SERIAL, ...)
sub_rel (id SERIAL, base_id INT)
sub_sub_rel (id SERIAL, sub_id INT)

I need a to lookup all inheritance path in one query (lookup base_rel.id and sub_rel.id assuming that I know sub_sub_rel.id). The naive way does not work, because you cannot use computed values as argument to subqueries. So following example code fails:

SELECT 
    (SELECT sub_id FROM sub_sub_rel WHERE id = X) AS sub_id,
    (SELECT base_id FROM sub_rel WHERE id = sub_id) AS base_id;

But I need to know these IDs in one query. There is relatively big inheritance tree, so I cannot create PL/pgSQL function for each table, but I can build queries dynamically. Is there a way to fetch these IDs?

As of recursive tables with self-referrental columns — these entities are totally different, for example 'User extends Group extends Server default'.

P.S.: A reason to have these IDs in one query is inheritance table with data like this:

 entity_type | entity_id | priority | value
-------------|-----------|----------|-------
  1          | 2         | 10       | xxx
  2          | 20        | 20       | yyy
  3          | 10        | 30       | zzz

Of course, I can build these IDs (10 of sub_sub_rel -> 20 of sub_rel -> 2 of base_rel) on application side and the fetch value with highest priority, but it will require N database round-trips because each ID depends on previous. So I seek a method to do this in one round-trip.

cdhowie · Accepted Answer · 2016-03-02 21:35:09Z

1

Given your schema, you could use a double join:

SELECT base_rel.id AS base_id,
       sub_rel.id AS sub_id,
       sub_sub_rel.id AS sub_sub_id

FROM sub_sub_rel

INNER JOIN sub_rel
ON sub_rel.id = sub_sub_rel.sub_id

INNER JOIN base_rel
ON base_rel.id = sub_rel.base_id;

With whatever WHERE clause makes sense in your particular case.

Note this query starts at the descendants and works up to the root, which means that root elements without children or grandchildren are not included in the result set.

If you want to start at the root and work your way down, particularly in cases where there may not be two levels of descendants, you need to select FROM base_rel and LEFT OUTER JOIN the other two tables:

SELECT base_rel.id AS base_id,
       sub_rel.id AS sub_id,
       sub_sub_rel.id AS sub_sub_id

FROM base_rel

LEFT OUTER JOIN sub_rel
ON sub_rel.base_id = base_rel.id

LEFT OUTER JOIN sub_sub_rel
ON sub_sub_rel.sub_id = sub_rel.id;

If all of the tables otherwise have the same schema (apart from the ID columns) then you could instead use one table with a self-referential column:

CREATE TABLE thing (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER NULL REFERENCES thing(id)
);

Using PostgreSQL's recursive query support, you can still fetch all of the rows in a generational relationship in one query, but you will have to use application-side logic to build them into a tree if this is desired.

For example, to select a particular row by ID but also include all ancestor rows:

WITH RECURSIVE thing_ancestors AS (
    SELECT thing.* FROM thing WHERE id = ?
    UNION
    SELECT thing.* FROM thing_ancestors
    INNER JOIN thing ON thing.id = thing_ancestors.parent_id
)
SELECT * FROM thing_ancestors;

You can use recursive queries to effectively aggregate data along the path to either the root or the leaf (depending on which direction your query goes). Consider the following tree:

1
+- 2
+- 3
   +- 4
5
+- 6
7

This would be represented in the following data:

INSERT INTO thing VALUES
  (1,NULL),
    (2,1),
    (3,1),
      (4,3),
  (5,NULL),
    (6,5),
  (7,NULL);

Now we can obtain the path to the item with id = 4 using the following recursive query, which starts at the leaf and progresses towards the root, leaving the final path on the root node (the row where parent_id IS NULL).

WITH RECURSIVE thing_ancestors AS (
    SELECT thing.*, thing.id || '' AS path FROM thing WHERE id = 4
    UNION
    SELECT thing.*, thing.id || '/' || path AS path FROM thing_ancestors
    INNER JOIN thing ON thing.id = thing_ancestors.parent_id
)
SELECT path FROM thing_ancestors WHERE parent_id IS NULL;

Which results in a single row with a single value of "1/3/4" -- perfect! (See sqlfiddle)

edited Mar 2, 2016 at 21:35

answered Mar 2, 2016 at 21:10

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Maxim Over a year ago

Thanks a lot! Also JOINs are usually easier to build in most ORMs that subqueries.

cdhowie Over a year ago

@Maxim See my updates -- if the schemas of those three tables are pretty much identical except for the IDs and parent relationship IDs, you could use a single table and recursive queries instead.

Maxim Over a year ago

See my updates too :) Entities are very different, but inherit some properties.

cdhowie Over a year ago

@Maxim I see. You may still be able to use table inheritance and recursive queries to do a lot of the heavy lifting for you, though.

Collectives™ on Stack Overflow

PostgreSQL nested suqueries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related