Playing with PostgreSQL and tree structures

tree-structure.sql

CREATE TABLE node (
  id INTEGER NOT NULL,
  parent_id INTEGER,
  UNIQUE(id, parent_id)
);
INSERT INTO node (id, parent_id) VALUES (0,NULL),(1,0),(2,0),(3,1),(4,1),(5,2),(6,2),(7,3),(8,3),(9,4),(10,4),(11,5),(12,5),(13,6),(14,6);


/* select all descendants of node 1 */
WITH RECURSIVE t(node, ancestors) AS (
  SELECT id, ARRAY[parent_id] FROM node WHERE parent_id = 1
UNION ALL
  SELECT n1.id, t.ancestors || ARRAY[n1.id]
  FROM node n1 JOIN t ON (n1.parent_id = t.node)
  WHERE NOT (n1.id = ANY (t.ancestors))
)
SELECT node
FROM t
ORDER BY node;


/* select all ancestors of node 14 */
WITH RECURSIVE t(node, parent) AS (
  SELECT id, parent_id FROM node WHERE id = 14
UNION ALL
  SELECT n1.id, n1.parent_id
  FROM node n1 JOIN t ON (n1.id = t.parent)
)
SELECT node
FROM t
WHERE node != 14
ORDER BY node;