Functions & operators

Where the functions come from

Everything DuckDB documents under duckdb.org/docs/sql/functions  is available: strftime, regexp_matches, list_aggregate, quantile_cont, string_agg, row_number() OVER (…), generate_series, and so on. Where a Postgres name differs from DuckDB’s, DuckDB usually accepts the Postgres alias too (substring, position, to_char via strftime, coalesce, nullif, greatest/least).

The shims below are the ones the engine adds or guarantees on top of that library because drivers and ORMs generate them.

Arithmetic

SELECT CAST(2.01 / 2 AS NUMERIC(10,2));              -- 1.01   (Postgres: 1.01)
SELECT ROUND(100.00 * 0.0325 / 12, 2);               -- 0.27   monthly interest, exact
SELECT 7 / 2;                                        -- 3.5    (Postgres: 3)  ← integer division differs
SELECT 1 / 0;                                        -- ERROR: division by zero (22012)
SELECT CAST(7 AS NUMERIC) / 2;                       -- 3.5    exact NUMERIC
SELECT amount / 0 FROM ledger;
-- ERROR:  division by zero   (SQLSTATE 22012)

Strings

DuckDB’s string library, including ||, concat, length, lower/upper, trim, split_part, regexp_replace, regexp_matches, regexp_extract, like/ilike, similar to, starts_with, left/right, repeat, format. LIKE is case-sensitive, ILIKE is not, as in Postgres. String comparison is byte-wise; there are no collations.

JSON

A JSON/JSONB column accepts the Postgres operators and DuckDB’s json_* functions:

SELECT payload->'sku'                       FROM events;   -- JSON
SELECT payload->>'sku'                      FROM events;   -- text
SELECT (payload->>'amount')::NUMERIC(12,2)  FROM events;   -- typed
SELECT json_extract(payload, '$.items[0].sku') FROM events;
SELECT json_extract_string(payload, '$.sku')   FROM events;
SELECT json_keys(payload)                       FROM events;
SELECT json_array_length(payload->'tags')       FROM events;
SELECT unnest(json_extract(payload, '$.tags[*]')) AS tag FROM events;
SELECT payload::json->'tags'->>0            FROM events;   -- Postgres-style cast round-trip

Paths use JSONPath ($.a.b[0], $.tags[*]). json_extract returns JSON, json_extract_string returns text. Nested STRUCT and ARRAY columns answer the same operators, because they are carried as JSON on the read path. @>, ? and the other Postgres containment operators are not available; express containment with json_extract comparisons.

For fields you filter or aggregate on constantly, promote them to typed columns and keep JSON for the long tail.

Vector operators

The pgvector distance operators work on VECTOR(n) columns and literals:

SELECT id FROM docs ORDER BY emb <-> '[0.12, -0.04, …]' LIMIT 10;       -- L2
SELECT id FROM docs ORDER BY emb <=> '[0.12, -0.04, …]'::vector LIMIT 5; -- cosine
SELECT a <#> b FROM pairs;                                               -- negative inner product

Exactly these three. A '[…]'::vector cast is accepted and dropped. Scores are distances: lower is closer. An ORDER BY <distance> LIMIT k uses an HNSW or IVF index when one exists (Schema changes → Indexes) and an exact scan otherwise. Session knobs: SET hnsw.ef_search = N, SET ivfflat.probes = N, SET dodil.vector_exact_scan = on (force exact), SET dodil.vector_consistency = strong | eventual. The full vector story, including index choice and recall, is on Vector.

Graph functions

Tables bound into a graph with CREATE GRAPH are traversable from SQL through the graph_* functions (k-hop, neighbours, shortest path, PageRank, connected components, BFS) and from Cypher over Bolt. They are documented on Graph → SQL functions.

Reserved words

DuckDB’s reserved words apply. The ones that collide with common column names: at, end, order, table, to, user. A bare at TIMESTAMP in CREATE TABLE, or WHERE at > …, is 42601. Double-quote the identifier everywhere ("at"), or choose a name such as occurred_at.