Get all parents for a child in postgresql

Question

I have been runed success On SQL Server. My System run on SQL Server and postgresql.

But when I change from SQL Server to postgresql it running fail.

My sql like this:

with name_tree as 
(
    SELECT DepartNo, ParentNo FROM Departments 
    WHERE DepartNo IN (
        SELECT DepartNo FROM BelongToDepartment 
        WHERE UserNo = 1)
   union all
   select C.DepartNo, C.ParentNo
   from Departments c
   join name_tree p on C.DepartNo = P.ParentNo  
    AND C.DepartNo<>C.ParentNo 
) 
select * from name_tree

The error is:

name_tree doesn't exist

How can get all parents for a child in postgresql

More detail in MSQ Server here

The SQL standard requires the `RECURSIVE` keyword for recursive common table expressions, and Postgres honors the standard. So you need to use `with recursive ...` [as documented in the manual](https://www.postgresql.org/docs/current/queries-with.html) — , Nov 30 '18 at 07:56

score 1 · Accepted Answer · answered Nov 30 '18 at 08:15

Try:

with RECURSIVE name_tree as 
(
    SELECT DepartNo, ParentNo FROM Departments 
    WHERE DepartNo IN (
        SELECT DepartNo FROM BelongToDepartment 
        WHERE UserNo = 1)
   union all
   select C.DepartNo, C.ParentNo
   from Departments c
   join name_tree p on C.DepartNo = P.ParentNo  
    AND C.DepartNo<>C.ParentNo 
) 
select * from name_tree

Get all parents for a child in postgresql

1 Answers1