CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
kapibara
kapibara

Reputation: 37

SQLite to Postgres Hierarchy Query

Here's a query I have successfully used with SQLite. It creates a hierarchy of all links that belong to a "Pak". 

WITH LinkTree(link_id, link_pid, pak_id, link_name, depth)
AS
(
SELECT *, 0 AS depth FROM links
WHERE link_pid = 0

UNION ALL
SELECT l.*, lt.depth+1 AS depth FROM LinkTree lt
JOIN links l ON (lt.link_id = l.link_pid)
)
SELECT * FROM LinkTree WHERE pak_id = 1;

I am trying to modify it so it works with Postgres, but I am getting an error saying that "depth" is ambigious.

There are two simple tables used:

Paks: pak_id, pak_name
Links: link_id, link_pid, link_name, pak_id

All columns are integers, except the *_name's that are varchars.

Can anyone help me?

Upvotes: 1

Views: 70

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

I think you need RECURSIVE keyword:

WITH RECURSIVE LinkTree(link_id, link_pid, pak_id, link_name, depth)
AS
(
SELECT *, 0 AS depth FROM links
WHERE link_pid = 0

UNION ALL
SELECT l.*, lt.depth+1 AS depth FROM LinkTree lt
JOIN links l ON (lt.link_id = l.link_pid)
)
SELECT * FROM LinkTree WHERE pak_id = 1;

EDIT:

Do not use * in select:

WITH LinkTree(link_id, link_pid, pak_id, link_name, depth)
AS
(
  SELECT link_id, link_pid, pak_id, link_name, 0 AS depth 
  FROM links
  WHERE link_pid = 0
  UNION ALL
  SELECT l.link_id, l.link_pid, l.pak_id, l.link_name, lt.depth+1 AS depth 
  FROM LinkTree lt
  JOIN links l 
    ON lt.link_id = l.link_pid
)
SELECT * 
FROM LinkTree 
WHERE pak_id = 1;

Upvotes: 1

Related Questions