Common table expressions (WITH) allow recursive queries.

Setup:

--  DROP TABLE categories;
CREATE TABLE categories (
    category_id         uuid        NOT NULL DEFAULT uuid_generate_v4(),
    name                text        NOT NULL,
    parent_category_id  uuid,
    CONSTRAINT categories_pkey  PRIMARY KEY (category_id)
);

INSERT INTO categories (name, parent_category_id)
VALUES
    ('Tools', NULL),
    ('Furniture', NULL);

INSERT INTO categories (name, parent_category_id)
VALUES
    ('Hammers', (SELECT category_id FROM categories WHERE name = 'Tools')),
    ('Saws', (SELECT category_id FROM categories WHERE name = 'Tools')),
    ('Drills', (SELECT category_id FROM categories WHERE name = 'Tools')),
    ('Chairs', (SELECT category_id FROM categories WHERE name = 'Furniture')),
    ('Tables', (SELECT category_id FROM categories WHERE name = 'Furniture'));

INSERT INTO categories (name, parent_category_id)
VALUES
    ('Stools', (SELECT category_id FROM categories WHERE name = 'Chairs')),
    ('Sofas', (SELECT category_id FROM categories WHERE name = 'Chairs'));

The final structure looks like this:

Tools
    Hammers
    Saws
    Drills
Furniture
    Chairs
        Stools
        Sofas
    Tables

And finally, the recursive query:

-- get 'Chairs' category and all of its children
WITH RECURSIVE categories_tree
   (name, category_id, parent_category_id)
AS (
    SELECT c.name, c.category_id, c.parent_category_id
    FROM categories c
    WHERE c.category_id = (
        SELECT category_id
        FROM categories
        WHERE name = 'Chairs'
    )
  UNION ALL
    SELECT c.name, c.category_id, c.parent_category_id
    FROM categories c
    INNER JOIN categories_tree ct ON (ct.category_id = c.parent_category_id)
)
SELECT *
FROM categories_tree;

Will return rows for: Chairs, Sools, Sofas

Sources

• Postgres Guide
• PostgreSQL documentation