DuckDB 2.0 SQL

Execute statements with duckdb. If a session file from duckdb-session exists, supply it via -init.

Before a large query

To inspect result columns and types without executing a query, run DESCRIBE SELECT .... To compute full column statistics across all rows, run SUMMARIZE <table>. When restricting query output, never run LIMIT without an explicit ORDER BY; unordered limits return nondeterministic rows.

Lambdas

DuckDB disables single-arrow syntax; write lambda x: expr. Higher-order list functions comprise list_transform, list_filter, and list_reduce:

SELECT list_transform([1, 2, 3], lambda x: x + 1);
SELECT list_transform([10, 20], lambda x, i: i);       -- [1, 2], 1-based index

When tracking positions, use lambda x, i: for the element and its 1-based index. When encountering NULL, list_transform preserves NULL elements; list_filter drops elements whose predicate evaluates to NULL. Never write subqueries inside lambdas; subqueries raise a binder error. If legacy syntax is required to unblock queries, run SET lambda_syntax = 'enable_single_arrow'; (removed in 2.1). To filter projection columns by name, write COLUMNS(lambda c: ...); supply an optional second argument lambda c, i: for the 1-based column index.

Division and decimals

SELECT TRY(1 // 0);  -- returns NULL

To catch division by zero on integer division or modulo without failing, wrap expressions in TRY(...). Avoid SET error_on_division_by_zero = false; this setting is deprecated and emits warnings.

Because avg(DECIMAL) and standard / operations cast operands to DOUBLE, run dedicated decimal functions for exact arithmetic:

• To sum without precision loss, use sum(x).
• To compute an exact mean, use decimal_average(x).
• To divide exact values, use decimal_division(x, y). Cast both operands explicitly to DECIMAL. On 2.0.0 an INTEGER argument is mis-bound and can overflow or return a wrong value: decimal_division(10.00, 4) raises result out of range although 2.5 fits the inferred DECIMAL(15,13), and decimal_division(127, 4.00) returns 0. decimal_division(10.00, 4::DECIMAL(10,0)) returns 2.5. The upstream fix is duckdb/duckdb#28420, labeled for a 2.0 backport; keep the casts until a released binary passes the probe.

Variables

SET VARIABLE x = 7;
SELECT $x, getvariable('x');
SET VARIABLE "my var" = 9;
SELECT $"my var";

To define a session variable, run SET VARIABLE name = value;. Query variables using $name or getvariable('name'). For identifiers containing spaces or special characters, quote with double quotes: SET VARIABLE "my var" = 9; SELECT $"my var";. When querying an unassigned variable, $undefined raises Values were not provided ..., whereas getvariable('undefined') returns NULL. In prepared statements, parameter placeholders ($x) fall back to session variables of the same name when omitted from execution arguments.

Nested schemas

CREATE SCHEMA sales;
CREATE SCHEMA sales.europe;
CREATE TABLE sales.europe.orders (id INTEGER);
SELECT * FROM sales.europe.orders;

When creating a nested schema, create the parent schema first (CREATE SCHEMA sales; CREATE SCHEMA sales.europe;). Nested schemas cannot sit on the search path: USE sales.europe, SET search_path = 'sales.europe', and SET schema = 'sales.europe' all fail. Two-part references resolve from the catalog root, not relative to the current schema; running SELECT * FROM europe.orders after USE sales fails. Running SHOW TABLES inside sales lists only immediate tables, omitting sales.europe.*. To inspect full schema paths, join duckdb_tables() with duckdb_schemas() on schema_oid = oid, exposing schema_name, parent_schema, and parent_schema_oid. To drop a schema tree, specify CASCADE: DROP SCHEMA sales CASCADE. A bare DROP SCHEMA sales fails while child schemas exist. If a top-level schema name requires an embedded dot, quote the identifier: CREATE SCHEMA "sales.europe" creates a single top-level schema where USE "sales.europe" succeeds.

Triggers

A trigger body must consist of exactly one data-modifying statement: INSERT, UPDATE, DELETE, or MERGE, optionally prefixed by WITH. Any SELECT, DDL, COPY, CALL, or PRAGMA body causes a parser error. A body modifying its own target table raises Recursive trigger chains are not yet supported.

On any single table, never mix FOR EACH STATEMENT and FOR EACH ROW triggers. When attaching triggers to temporary tables, reference only temporary objects; modifying main-catalog tables raises a catalog binder error. DuckDB does not support BEFORE ... FOR EACH ROW, AFTER UPDATE ... FOR EACH ROW, row triggers with REFERENCING, WHEN, INSTEAD OF, or CREATE TEMP TRIGGER.

For the trigger support matrix, RETURNING rules, execution order, and metadata queries, read references/triggers.md.

DML proof

When executing UPDATE, DELETE, or MERGE, follow this verification sequence:

1. BEGIN transaction.
2. Count matching predicate rows. Before executing MERGE or UPDATE ... FROM, deduplicate the source table: a target row matching multiple source rows causes a runtime error in MERGE and updates from an arbitrary source row in UPDATE ... FROM.
3. Run the DML statement. If a matching row trigger exists on the table, DuckDB blocks RETURNING (see event rules in references/triggers.md). When RETURNING is blocked, verify modifications via before-and-after row counts or CLI .changes.
4. COMMIT, then re-count. Treat zero rows affected as an operation result, not proof of success. Never use RETURNING as the sole proof when row triggers exist.

2.0 syntax worth using

COPY ... FROM cannot serve as a CTE body. A COPY ... TO inside a CTE writes the file and returns the number of rows written; Python execute(...).fetchall() returns [(n,)]. On 2.0.0 the CLI does not render that count in JSON, CSV, or box output, so empty CLI output does not mean zero rows. In the CLI, run .changes on to print the count, or add RETURN_STATS or RETURN_FILES to the COPY options to get rendered rows. Prove the write by counting the output file.

Result traps

For friendly-SQL patterns and edge-case examples behind these traps, read references/friendly-sql.md.

Parser

The PEG parser is active by default; syntax errors report LINE n: with an exact position caret. If a query that parsed in 1.5 fails under 2.0, run SET enable_legacy_parser = true; to autoload the extension and switch parsers for the session. Note that the legacy parser rejects simplified FROM ... NEAREST syntax; explicit INNER JOIN ... NEAREST still parses. Treat the legacy parser strictly as a temporary syntax fallback to unblock migrations, not a semantic time machine. File an upstream issue, unblock immediately, and remove the setting from production SQL.

Before optimizing

When reading execution plans, comparing EXPLAIN against EXPLAIN ANALYZE, profiling operator bottlenecks, or diagnosing memory spills, hand off to duckdb-profile. EXPLAIN (SQL) belongs here because it decompiles optimized query representations back into executable SQL rather than reporting runtime profile metrics.

Verify

SELECT list_transform([1, 2, 3], lambda x: x + 1) AS lambda,
       TRY(1 // 0) AS safe_divide,
       decimal_division(10.00, 4.00) AS exact;

Expect [2, 3, 4], NULL, and 2.500000.

References

• Friendly SQL owns clause syntax. Detailed examples are in references/friendly-sql.md.
• Lambda functions owns lambda syntax and function families.
• CREATE TRIGGER owns trigger specifications. Support matrices and RETURNING rules are in references/triggers.md.
• Nested schemas owns schema path resolution.
• FROM and JOIN owns NEAREST, ASOF, and POSITIONAL.
• WITH owns data-modifying CTE specifications.
• MERGE INTO and UPDATE own multi-match rules.
• PEG parser and legacy parser own parser fallback settings.
• Friendly-SQL origins trace to the query skill in https://github.com/duckdb/duckdb-skills (MIT). Lambda, trigger, schema, division, and multi-match behaviors were verified on DuckDB 2.0.0.