dacracot dacracot - 8 months ago 68
SQL Question

What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

In Oracle, if I have a table defined as …

CREATE TABLE taxonomy
(
key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY,
value VARCHAR2(255),
taxHier NUMBER(11)
);
ALTER TABLE
taxonomy
ADD CONSTRAINT
taxTaxFkey
FOREIGN KEY
(taxHier)
REFERENCES
tax(key);


With these values …

key value taxHier
0 zero null
1 one 0
2 two 0
3 three 0
4 four 1
5 five 2
6 six 2


This query syntax …

SELECT
value
FROM
taxonomy
CONNECT BY
PRIOR key = taxHier
START WITH
key = 0;


Will yield …

zero
one
four
two
five
six
three


How is this done in PostgreSQL?

Answer Source

Use a RECURSIVE CTE in Postgres:

WITH RECURSIVE cte AS (
   SELECT key, value, 1 AS level
   FROM   taxonomy
   WHERE  key = 0

   UNION  ALL
   SELECT t.key, t.value, c.level + 1
   FROM   cte      c
   JOIN   taxonomy t ON t.taxHier = c.key
   )
SELECT value
FROM   cte
ORDER  BY level;

Details and links to documentation in my previous answer:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download