DML & Queries

SQL Reference: DML

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

INSERT

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;

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

UPDATE

SQL

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

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. 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
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;

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

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

All standard aggregate functions can also be used as window functions.

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;

Subqueries

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

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