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

Question

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 1

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:

• Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?