"recursive" query in postgresql

Question

this is my current query :

SELECT
    id, name,
    CASE 
        when geolocation='' then (select geolocation from location where id = q1.parent_id)
        else geolocation end
FROM location q1;

this is an example of the table on which i make my query and the current result i get:

id | name | geolocation | parent_id 
-- | ---- | ----------- | ---------
 1 | aaaa |   0.0,0.0   |    
 2 | bbbb |   1.1,1.1   |     1
 3 | cccc |             |     1
 4 | dddd |   2.2,2.2   |  
 5 | eeee |             |
 6 | ffff |             |     3

id | name | geolocation 
-- | ---- | ----------- 
 1 | aaaa |   0.0,0.0   
 2 | bbbb |   1.1,1.1   
 3 | cccc |   0.0,0.0        
 4 | dddd |   2.2,2.2    
 5 | eeee |   
 6 | ffff |

this is not the result i want to get. what i would like is the request to be "recursive" so that the 6th location gets the geolocation of the "root parent" (in this case location aaaa). so my expected result is :

id | name | geolocation 
-- | ---- | ----------- 
 1 | aaaa |   0.0,0.0   
 2 | bbbb |   1.1,1.1   
 3 | cccc |   0.0,0.0        
 4 | dddd |   2.2,2.2    
 5 | eeee |   
 6 | ffff |   0.0,0.0

i tried to use the "cte method" but i can't get the result i want...

anyone has an idea?

best regards and have a great day

ps : careful, geolocation is a string

If you need to support a hierarchical structure of data - this might help https://stackoverflow.com/a/38701519/5962802 — IVO GELOV, Jul 29 '21 at 09:05

score 1 · Accepted Answer · answered Jul 29 '21 at 11:55

\i tmp.sql

CREATE TABLE wtf
        ( id integer PRIMARY KEY
        , name text
        , geolocation text
        , parent_id integer REFERENCES wtf(id)
        );

INSERT INTO wtf
        ( id , name , geolocation , parent_id ) VALUES
  (1 ,'aaaa' ,'0.0,0.0',NULL)
 , (2 ,'bbbb' ,'1.1,1.1',1)
 , (3 ,'cccc' ,'',1)
 , (4 ,'dddd' ,'2.2,2.2', NULL )
 , (5 ,'eeee' ,'',NULL)
 , (6 ,'ffff' ,'',3 )
        ;

SELECT * FROM wtf;

        -- find the "root" (if any) for all entries
WITH RECURSIVE opa AS (
        SELECT id AS opa
        , id AS moi
        FROM wtf
        WHERE parent_id IS NULL -- The roots
    UNION
        SELECT o.opa AS opa -- inherit the root name from your parent
                ,m.id AS moi
        FROM wtf m -- the descendants
        JOIN opa o ON (m.parent_id = o.moi)
        )
SELECT w.*
        , x.name AS opa_name
        , x.geolocation AS opa_geolocation
FROM wtf w
JOIN opa o ON (w.id = o.moi)
LEFT JOIN wtf x ON (x.id = o.opa)
        ;

Results:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 6
 id | name | geolocation | parent_id 
----+------+-------------+-----------
  1 | aaaa | 0.0,0.0     |          
  2 | bbbb | 1.1,1.1     |         1
  3 | cccc |             |         1
  4 | dddd | 2.2,2.2     |          
  5 | eeee |             |          
  6 | ffff |             |         3
(6 rows)

 id | name | geolocation | parent_id | opa_name | opa_geolocation 
----+------+-------------+-----------+----------+-----------------
  1 | aaaa | 0.0,0.0     |           | aaaa     | 0.0,0.0
  4 | dddd | 2.2,2.2     |           | dddd     | 2.2,2.2
  5 | eeee |             |           | eeee     | 
  2 | bbbb | 1.1,1.1     |         1 | aaaa     | 0.0,0.0
  3 | cccc |             |         1 | aaaa     | 0.0,0.0
  6 | ffff |             |         3 | aaaa     | 0.0,0.0
(6 rows)

thank you for your answer @wildplasser, works perfectly fine. just added this query in the end to get the final result : ``` SELECT id, name, CASE when geolocation = '' then opa_geolocation else geolocation end as geolocation FROM this_intermediate ORDER BY id asc; ``` thank you again and have a great day, best regards. — Armand Violle, Jul 29 '21 at 12:15
Yes, you could use `COALESCE()` to massage the final results. (cleaner than the CASE expression) NOTE: `opa` is Dutch for `grandpere` ;-) — wildplasser, Jul 29 '21 at 12:33

Mitko Keckaroski · Answer 2 · 2021-07-29T12:11:28.360

0

The first idea is to use a function.

CREATE OR REPLACE  FUNCTION get_last_location(int) RETURNS text
    AS $$ 

       WITH RECURSIVE subordinates AS 
        (
         SELECT
         id,parent_id, name,geolocation from location 
         WHERE
         id =$1

         UNION

         SELECT
           e.id,e.parent_id, e.name,e.geolocation from location e
          inner JOIN subordinates s ON s.parent_id = e.id
        )
    select geolocation from subordinates 
    where not (geolocation is null) order by id limit 1
    
    $$
    LANGUAGE SQL;

Then use this select:

SELECT
    id, name,
    CASE 
        when geolocation is null then (select get_last_location(q1.id))
        else geolocation end
FROM location q1;

The query without using function

SELECT
    id, name,
    CASE 
        when geolocation is null then 
        (
               WITH RECURSIVE subordinates AS 
                  (
                   SELECT
                   id,parent_id, name,geolocation from location 
                   WHERE
                   id =q1.id

                   UNION

                   SELECT
                     e.id,e.parent_id, e.name,e.geolocation from location e
                    inner JOIN subordinates s ON s.parent_id = e.id
                  )
                  select geolocation from subordinates 
                  where not (geolocation is null) order by id limit 1
        
        )
        else geolocation end
FROM location q1;

The Output

edited Jul 29 '21 at 12:11

answered Jul 29 '21 at 09:22

Mitko Keckaroski

954
1
8
12

hi @MitkoKeckaroski, thank you for your answer, but it only works with id 1 or am I wrong ? i would like something "generic" that does it for each parent... – Armand Violle Jul 29 '21 at 09:28
just add the id of any element in the WERE condition – Mitko Keckaroski Jul 29 '21 at 09:30
ok i see, the problem is that my table is dynamic so that would not work... – Armand Violle Jul 29 '21 at 09:31
no idea on how to do it without specifying the "root" id ? – Armand Violle Jul 29 '21 at 09:31
1

@ArmandViolle: typically the "root id" is defined by using `where parent_id is null` – Jul 29 '21 at 09:33
i'm dumb... i'm tryning your query with this "where" clause. tell you if it works – Armand Violle Jul 29 '21 at 09:38
still doesn't work.. sorry for wasting your time – Armand Violle Jul 29 '21 at 09:42
@MitkoKeckaroski thank you, last question : is there a way to do it without creating a function ? – Armand Violle Jul 29 '21 at 11:56
@Armand Violle, yes, check in the answer – Mitko Keckaroski Jul 29 '21 at 12:13

Andrei Odegov · Answer 3 · 2021-07-29T12:22:47.727

0

Try the following query:

  r AS (
      SELECT id, name, geolocation
      FROM location
      WHERE parent_id IS NULL
    UNION ALL
      SELECT
        l.id,
        l.name,
        COALESCE(l.geolocation,
                 r.geolocation)
      FROM r
      JOIN location AS l
        ON r.id = l.parent_id
  )
SELECT * FROM r;

Result:

+----+------+-------------+
| id | name | geolocation |
+----+------+-------------+
|  1 | aaaa | 0.0,0.0     |
|  4 | dddd | 2.2,2.2     |
|  5 | eeee |             |
|  2 | bbbb | 1.1,1.1     |
|  3 | cccc | 0.0,0.0     |
|  6 | ffff | 0.0,0.0     |
+----+------+-------------+

db<>fiddle

edited Jul 29 '21 at 12:22

answered Jul 29 '21 at 12:15

Andrei Odegov

2,925
2
15
21

Ogedov I tried the exact same thing before asking the question thinking it would work too, but it doesn't... thanks for your answer – Armand Violle Jul 29 '21 at 12:21

"recursive" query in postgresql

3 Answers3