"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

• IVO GELOV: If you need to support a hierarchical structure of data - this might help stackoverflow.com/a/38701519/5962802

Answer 1

\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)

• Armand Violle: 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.
• wildplasser: Yes, you could use COALESCE() to massage the final results. (cleaner than the CASE expression) NOTE: opa is Dutch for grandpere ;-)

Answer 2

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

enter image description here

• Armand Violle: 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...
• Mitko Keckaroski: just add the id of any element in the WERE condition
• Armand Violle: ok i see, the problem is that my table is dynamic so that would not work...
• Armand Violle: no idea on how to do it without specifying the "root" id ?
• user330315: @ArmandViolle: typically the "root id" is defined by using where parent_id is null

Answer 3

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

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