DML & Queries

SQL Reference: DML

Data Manipulation Language statements for inserting, updating, and deleting data.

INSERT

SQL

-- With RETURNING
INSERT INTO users (name, email) VALUES ('Dave', 'dave@example.com')
ON CONFLICT (email) DO NOTHING RETURNING id, name, email;

▶ Run

SQL

-- Single row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Multi-row
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

-- INSERT ... SELECT
INSERT INTO archive SELECT * FROM users WHERE active = false;

-- With RETURNING
INSERT INTO users (name) VALUES ('Alice') RETURNING id, name;

INSERT ON CONFLICT (Upsert)

SQL

-- Skip conflicting rows
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- Upsert by column
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE SET name = excluded.name;

-- Upsert by constraint name
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET name = excluded.name;

▶ Run

The excluded pseudo-table is available in DO UPDATE SET clauses, referencing the row that was proposed for insertion.

No MERGE

MERGE is not supported. Every form, including WITH ... MERGE, fails with 0A000 (MERGE is not supported) before any table is read, and a MERGE inside a LANGUAGE sql function body is accepted by CREATE FUNCTION but fails the same way when the function is called. Write upserts with INSERT ... ON CONFLICT ... DO UPDATE as above; a WHEN MATCHED THEN DELETE branch needs a separate DELETE.

UPDATE

SQL

UPDATE users SET active = true WHERE name = 'Charlie' RETURNING *;

▶ Run

SQL

UPDATE users SET name = 'Bob' WHERE id = 1;
UPDATE users SET name = 'Bob' WHERE id = 1 RETURNING *;

▶ Run

DELETE

SQL

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE id = 1 RETURNING *;

Foreign Key Referential Actions

Supported actions for ON DELETE and ON UPDATE: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION. A referencing table without a primary key cannot declare ON DELETE SET NULL / SET DEFAULT or ON UPDATE CASCADE / SET NULL / SET DEFAULT (0A000); give it a primary key first. Recursive cascade operations include cycle detection. MATCH SIMPLE semantics (PostgreSQL default): if any referencing column is NULL, the FK check is skipped.

---

SQL Reference: Queries

SELECT query features, JOINs, window functions, CTEs, subqueries, and set operations.

JOINs

Join Type	Supported
INNER JOIN	Yes
LEFT [OUTER] JOIN	Yes
RIGHT [OUTER] JOIN	Yes
FULL [OUTER] JOIN	Yes
CROSS JOIN	Yes
LATERAL JOIN	Yes — including correlated bodies, a WITH (or WITH RECURSIVE) clause inside the body, and correlated scalar/EXISTS/IN/ANY/ARRAY subqueries inside the body; see the compatibility matrix
Semi join (via EXISTS)	Yes
Anti join (via NOT EXISTS)	Yes

Both hash join and nested-loop join implementations are available. The optimizer uses cost-based join reordering (DPccp algorithm).

Aggregation

• GROUP BY with hash-based aggregation
• HAVING clause
• DISTINCT aggregates: COUNT(DISTINCT col)
• FILTER clause: COUNT(*) FILTER (WHERE x > 0)
• GROUPING() function

Window Functions

SQL

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

▶ Run

Supported ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST

Supported access functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE

Only SUM, AVG, COUNT, MIN, MAX and STRING_AGG may also be used as window functions — every other aggregate fails with an OVER clause, with an error that names the function. See Window Functions for the measured list.

Common Table Expressions (CTEs)

SQL

-- Standard CTE
WITH active_users AS (
    SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users;

-- Recursive CTE
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

▶ Run

Recursive CTEs run for at most 1,000 iterations; beyond that the query fails with 54001 — statement exceeds server complexity limit.

Every definition in one WITH list needs a distinct name: a duplicate fails with 42712 (WITH query name "a" specified more than once), as in PostgreSQL. A name can still be reused across separate scopes — two sibling subqueries that each define a return their own values, and a nested WITH a shadows an outer one without disturbing it.

A WITH list may contain INSERT/UPDATE/DELETE … RETURNING definitions in any position, including after a helper definition they read from. This works the same over the HTTP SQL API (which is what db9 db sql uses) and the PostgreSQL wire protocol:

SQL

WITH src AS (SELECT id FROM staging WHERE ready),
     ins AS (INSERT INTO target SELECT id FROM src RETURNING id)
SELECT count(*) FROM ins;

A WITH definition that is never referenced is not executed, as in PostgreSQL — a CTE that calls a volatile function such as nextval() has no effect when nothing selects from it.

Subqueries

• Scalar subqueries
• EXISTS / NOT EXISTS
• IN / NOT IN with subquery
• ANY / ALL with subquery
• Array subqueries
• Correlated subqueries (with subquery decorrelation optimization)

All of these also work inside a LATERAL body, correlated or not, in any position — select list, VALUES element, WHERE, GROUP BY, HAVING, ORDER BY or a function argument.

Set Operations

Operation	Supported
UNION / UNION ALL	Yes
INTERSECT / INTERSECT ALL	Yes
EXCEPT / EXCEPT ALL	Yes

Other Query Features

• SELECT DISTINCT and SELECT DISTINCT ON (expr)
• ORDER BY with ASC/DESC and NULLS FIRST/NULLS LAST
• LIMIT and OFFSET
• EXPLAIN and EXPLAIN ANALYZE for query plans
• PREPARE name AS ... / EXECUTE name (params) / DEALLOCATE name
• generate_series() table function
• SHOW TABLES