Comment by dude_abides

Another cool feature: recursive queries using the WITH common table expressions.

Say you have a table fs <id, name, parent_id> representing a hierarchical filesystem, and you want to print the full path of each file, here's how you can do it in a single query:

  WITH RECURSIVE path(id, name, parent_id, path, parent) AS (
    SELECT id, name, parent_id, '/', NULL FROM fs WHERE id = 1 -- base case
    UNION
    SELECT fs.id, fs.name, fs.parent_id, parentpath.path || 
      CASE parentpath.path WHEN '/' THEN '' ELSE '/' END || 
      fs.name as path, parentpath.path as parent
      FROM fs INNER JOIN path AS parentpath ON fs.parent_id = parentpath.id
  ) SELECT id, name FROM path;