SQL Reference

ParticleDB speaks PostgreSQL-compatible SQL with extensions for vector search, time-series, and hybrid transactional/analytical workloads. Connect with any PostgreSQL client (psql, JDBC, node-postgres, etc.) and use the SQL you already know.

Statements

Data Definition (DDL)

Statement	Description	Example
`CREATE TABLE`	Create a new table	`CREATE TABLE orders (id BIGINT PRIMARY KEY, total FLOAT64)`
`DROP TABLE`	Remove a table	`DROP TABLE IF EXISTS orders`
`ALTER TABLE`	Modify table schema	`ALTER TABLE orders ADD COLUMN status TEXT`
`TRUNCATE`	Remove all rows from a table	`TRUNCATE TABLE orders`
`CREATE INDEX`	Create a B-tree or vector index	`CREATE INDEX idx_total ON orders (total)`
`DROP INDEX`	Remove an index	`DROP INDEX IF EXISTS idx_total`
`CREATE VIEW`	Define a named query	`CREATE VIEW high_value AS SELECT * FROM orders WHERE total > 1000`
`DROP VIEW`	Remove a view	`DROP VIEW IF EXISTS high_value`
`CREATE TYPE ... AS ENUM`	Define an enumerated type	`CREATE TYPE status AS ENUM ('pending', 'shipped', 'delivered')`
`DROP TYPE`	Remove a user-defined type	`DROP TYPE IF EXISTS status`
`CREATE PROCEDURE`	Define a stored procedure	`CREATE PROCEDURE archive(cutoff INT64) AS $$ ... $$`
`DROP PROCEDURE`	Remove a stored procedure	`DROP PROCEDURE IF EXISTS archive`
`CREATE FUNCTION`	Define a user-defined function	`CREATE FUNCTION double(x INT64) RETURNS INT64 AS $$ ... $$`
`DROP FUNCTION`	Remove a user-defined function	`DROP FUNCTION IF EXISTS double`
`ANALYZE`	Refresh table statistics	`ANALYZE orders`

See DDL for full syntax.

Data Manipulation (DML)

Statement	Description	Example
`SELECT`	Query data	`SELECT name, price FROM products WHERE price > 50`
`INSERT`	Add rows	`INSERT INTO products (id, name) VALUES (1, 'Widget')`
`INSERT ... ON CONFLICT`	Upsert	`INSERT INTO products ... ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name`
`UPDATE`	Modify existing rows	`UPDATE products SET price = 19.99 WHERE id = 1`
`DELETE`	Remove rows	`DELETE FROM products WHERE discontinued = true`
`COPY FROM`	Bulk import (CSV, Parquet, JSON)	`COPY products FROM '/data/products.csv' WITH (FORMAT CSV)`
`COPY TO`	Bulk export	`COPY products TO '/data/export.parquet' WITH (FORMAT PARQUET)`
`INSERT INTO ... SELECT`	Insert from a query	`INSERT INTO archive SELECT * FROM orders WHERE created_at < '2025-01-01'`

See SELECT, INSERT / UPDATE / DELETE.

Transactions

Statement	Description	Example
`BEGIN`	Start a transaction	`BEGIN`
`COMMIT`	Commit the current transaction	`COMMIT`
`ROLLBACK`	Abort the current transaction	`ROLLBACK`
`SAVEPOINT`	Create a named savepoint	`SAVEPOINT before_update`
`RELEASE SAVEPOINT`	Release a savepoint	`RELEASE SAVEPOINT before_update`
`PREPARE TRANSACTION`	Prepare for two-phase commit	`PREPARE TRANSACTION 'tx_001'`
`COMMIT PREPARED`	Commit a prepared transaction	`COMMIT PREPARED 'tx_001'`
`ROLLBACK PREPARED`	Rollback a prepared transaction	`ROLLBACK PREPARED 'tx_001'`

See Transactions.

Access Control

Statement	Description	Example
`CREATE ROLE`	Create a role	`CREATE ROLE analyst`
`CREATE USER`	Create a user (role with login)	`CREATE USER bob WITH PASSWORD 'secret'`
`DROP ROLE`	Remove a role	`DROP ROLE IF EXISTS analyst`
`GRANT`	Grant privileges or roles	`GRANT SELECT ON orders TO analyst`
`REVOKE`	Revoke privileges or roles	`REVOKE INSERT ON orders FROM analyst`
`ALTER ROLE`	Modify role properties	`ALTER ROLE bob SET PASSWORD 'new_secret'`
`CREATE POLICY`	Define row-level security policy	`CREATE POLICY tenant_iso ON orders FOR SELECT USING (tenant_id = current_user_attr('tenant'))`
`DROP POLICY`	Remove an RLS policy	`DROP POLICY tenant_iso ON orders`

See DDL for full syntax.

Utility

Statement	Description	Example
`EXPLAIN`	Show the query plan	`EXPLAIN SELECT * FROM orders WHERE id = 42`
`EXPLAIN ANALYZE`	Execute and show actual timings	`EXPLAIN ANALYZE SELECT COUNT(*) FROM orders`
`SET`	Set a session variable	`SET search_path = 'public'`
`SHOW`	Show a session variable	`SHOW server_version`
`LISTEN`	Subscribe to a notification channel	`LISTEN order_updates`
`UNLISTEN`	Unsubscribe from a channel	`UNLISTEN order_updates`
`NOTIFY`	Send a notification	`NOTIFY order_updates, 'order 42 shipped'`
`CALL`	Execute a stored procedure	`CALL archive(30)`

Data Types

Type	Aliases	Description
`INT64`	`INT`, `BIGINT`, `SMALLINT`, `TINYINT`	64-bit signed integer
`FLOAT64`	`FLOAT`, `DOUBLE`, `REAL`, `DOUBLE PRECISION`	64-bit IEEE 754 floating point
`BOOLEAN`	`BOOL`	`TRUE` or `FALSE`
`TEXT`	`VARCHAR`, `STRING`, `CHAR`	Variable-length UTF-8 string
`BYTES`	`BYTEA`, `BLOB`, `BINARY`	Variable-length byte array
`TIMESTAMP`		Microsecond-precision UTC timestamp
`VECTOR(n)`		Dense array of `n` 32-bit floats for similarity search
`JSON`	`JSONB`	JSON document stored as text
`ARRAY`		Array of scalar values
`ENUM`		User-defined enumerated type (via `CREATE TYPE`)

See Data Types for casting, storage details, and examples.

Operators

Comparison

Operator	Description	Example
`=`	Equal	`price = 9.99`
`!=` or `<>`	Not equal	`status != 'cancelled'`
`<`	Less than	`age < 30`
`<=`	Less than or equal	`price <= 100`
`>`	Greater than	`score > 90`
`>=`	Greater than or equal	`quantity >= 5`

Logical

Operator	Description	Example
`AND`	Logical conjunction	`price > 10 AND price < 50`
`OR`	Logical disjunction	`status = 'active' OR status = 'pending'`
`NOT`	Logical negation	`NOT is_deleted`

Arithmetic

Operator	Description	Example
`+`	Addition	`price + tax`
`-`	Subtraction	`total - discount`
`*`	Multiplication	`quantity * price`
`/`	Division	`total / count`
`%`	Modulo	`id % 10`

String

Operator	Description	Example
`\|\|`	String concatenation	`first_name \|\| ' ' \|\| last_name`
`LIKE`	Pattern match (case-sensitive)	`name LIKE 'Jo%'`
`NOT LIKE`	Negated pattern match	`email NOT LIKE '%@spam.com'`
`ILIKE`	Pattern match (case-insensitive)	`name ILIKE '%smith%'`

Predicates

Predicate	Description	Example
`IS NULL`	Test for null	`email IS NULL`
`IS NOT NULL`	Test for non-null	`phone IS NOT NULL`
`IN (...)`	Set membership	`status IN ('active', 'pending')`
`NOT IN (...)`	Negated set membership	`id NOT IN (1, 2, 3)`
`BETWEEN ... AND`	Range test (inclusive)	`price BETWEEN 10 AND 50`
`EXISTS (subquery)`	Test for non-empty subquery	`EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)`
`ANY` / `SOME`	Compare to any subquery row	`price > ANY (SELECT min_price FROM tiers)`
`ALL`	Compare to all subquery rows	`score >= ALL (SELECT threshold FROM rules)`
`CONTAINS(s, sub)`	True if string contains substring	`CONTAINS(email, '@gmail.com')`

JSON

Operator	Description	Example
`->`	Extract JSON element (returns JSON)	`data -> 'address'`
`->>`	Extract JSON element (returns text)	`data ->> 'name'`

Vector

Operator	Description	Example
`<=>`	Vector distance (for ORDER BY)	`ORDER BY embedding <=> '[0.1, 0.2, ...]' LIMIT 10`

Scalar Functions

Math

Function	Description	Example
`ABS(x)`	Absolute value	`ABS(-42)` — `42`
`ROUND(x [, d])`	Round to `d` decimal places	`ROUND(3.14159, 2)` — `3.14`
`CEIL(x)` / `CEILING(x)`	Round up to nearest integer	`CEIL(2.3)` — `3.0`
`FLOOR(x)`	Round down to nearest integer	`FLOOR(2.7)` — `2.0`
`PI()`	Pi constant (3.14159…)	`PI()` — `3.141592653589793`
`SQRT(x)`	Square root	`SQRT(144)` — `12.0`
`POWER(base, exp)` / `POW`	Exponentiation	`POWER(2, 10)` — `1024.0`
`EXP(x)`	Natural exponential (e^x)	`EXP(1)` — `2.718...`
`LN(x)`	Natural logarithm	`LN(2.718)` — `~1.0`
`LOG(x)`	Natural logarithm (single arg)	`LOG(2.718)` — `~1.0`
`LOG(base, x)`	Logarithm with explicit base	`LOG(2, 1024)` — `10.0`
`LOG10(x)`	Base-10 logarithm	`LOG10(100)` — `2.0`
`LOG2(x)`	Base-2 logarithm	`LOG2(8)` — `3.0`
`TRUNC(x [, n])`	Truncate to `n` decimal places (no rounding)	`TRUNC(3.14159, 3)` — `3.141`
`SIGN(x)`	Sign of x (-1, 0, or 1)	`SIGN(-7)` — `-1`
`CBRT(x)`	Cube root	`CBRT(27)` — `3.0`
`DIV(a, b)`	Integer (truncated) division	`DIV(7, 2)` — `3`
`MOD(a, b)`	Modulo (remainder)	`MOD(7, 3)` — `1`
`DEGREES(x)`	Radians to degrees	`DEGREES(3.14159)` — `~180.0`
`RADIANS(x)`	Degrees to radians	`RADIANS(180)` — `~3.14159`
`SIN(x)`	Sine	`SIN(0)` — `0.0`
`COS(x)`	Cosine	`COS(0)` — `1.0`
`TAN(x)`	Tangent	`TAN(0)` — `0.0`
`ASIN(x)`	Inverse sine	`ASIN(1)` — `~1.5708`
`ACOS(x)`	Inverse cosine	`ACOS(1)` — `0.0`
`ATAN(x)`	Inverse tangent	`ATAN(1)` — `~0.7854`
`ATAN2(y, x)`	Two-argument inverse tangent	`ATAN2(1, 1)` — `~0.7854`
`SINH(x)`	Hyperbolic sine	`SINH(1)` — `~1.1752`
`COSH(x)`	Hyperbolic cosine	`COSH(0)` — `1.0`
`TANH(x)`	Hyperbolic tangent	`TANH(0)` — `0.0`
`COT(x)`	Cotangent (1/tan)	`COT(1)` — `~0.6421`
`RANDOM()` / `RAND()`	Random float in [0.0, 1.0)	`RANDOM()`

String

Function	Description	Example
`UPPER(s)`	Convert to uppercase	`UPPER('hello')` — `'HELLO'`
`LOWER(s)`	Convert to lowercase	`LOWER('HELLO')` — `'hello'`
`LENGTH(s)` / `LEN` / `STRLEN`	String length	`LENGTH('hello')` — `5`
`SUBSTRING(s, start [, len])` / `SUBSTR`	Extract substring (1-based)	`SUBSTRING('hello', 2, 3)` — `'ell'`
`TRIM(s)`	Remove leading/trailing whitespace	`TRIM(' hi ')` — `'hi'`
`LTRIM(s)`	Remove leading whitespace	`LTRIM(' hi')` — `'hi'`
`RTRIM(s)`	Remove trailing whitespace	`RTRIM('hi ')` — `'hi'`
`LEFT(s, n)`	First `n` characters	`LEFT('hello', 3)` — `'hel'`
`RIGHT(s, n)`	Last `n` characters	`RIGHT('hello', 3)` — `'llo'`
`REPLACE(s, from, to)`	Replace all occurrences	`REPLACE('hello', 'l', 'r')` — `'herro'`
`REVERSE(s)`	Reverse a string	`REVERSE('abc')` — `'cba'`
`REPEAT(s, n)`	Repeat string `n` times	`REPEAT('ab', 3)` — `'ababab'`
`INITCAP(s)`	Capitalize first letter of each word	`INITCAP('hello world')` — `'Hello World'`
`CONCAT(a, b, ...)`	Concatenate strings (NULL-safe)	`CONCAT('a', NULL, 'b')` — `'ab'`
`POSITION(haystack, needle)` / `STRPOS`	Find substring position (1-based, 0 = not found)	`POSITION('hello', 'ell')` — `2`
`STARTS_WITH(s, prefix)`	True if string starts with prefix	`STARTS_WITH('hello', 'hel')` — `TRUE`
`ENDS_WITH(s, suffix)`	True if string ends with suffix	`ENDS_WITH('hello', 'llo')` — `TRUE`
`CONCAT_WS(sep, a, b, ...)`	Concatenate with separator (NULL-safe)	`CONCAT_WS('-', 'a', 'b', 'c')` — `'a-b-c'`
`LPAD(s, len [, fill])`	Left-pad string to length (fill defaults to space)	`LPAD('42', 5, '0')` — `'00042'`
`RPAD(s, len [, fill])`	Right-pad string to length (fill defaults to space)	`RPAD('hi', 5, '.')` — `'hi...'`
`CONTAINS(s, substr)`	True if `s` contains `substr`	`CONTAINS('hello', 'ell')` — `TRUE`
`SPLIT_PART(s, delim, n)`	Return the `n`th token after splitting by delimiter	`SPLIT_PART('a.b.c', '.', 2)` — `'b'`
`CHAR_LENGTH(s)` / `CHARACTER_LENGTH`	Character length (same as LENGTH)	`CHAR_LENGTH('hello')` — `5`
`OCTET_LENGTH(s)`	Length in bytes	`OCTET_LENGTH('hello')` — `5`
`BIT_LENGTH(s)`	Length in bits	`BIT_LENGTH('hello')` — `40`
`TRANSLATE(s, from, to)`	Replace characters by positional mapping	`TRANSLATE('hello', 'el', 'ip')` — `'hippo'`
`HEX(s)` / `TO_HEX(s)`	Encode string as hexadecimal	`TO_HEX('AB')`
`ASCII(s)`	ASCII code of the first character	`ASCII('A')` — `65`
`CHR(n)`	Character for the given ASCII code	`CHR(65)` — `'A'`
`REGEXP_REPLACE(s, pattern, replacement)`	Regex-based string replacement	`REGEXP_REPLACE(url, '^https?://([^/]+)/.*', '\1')`
`MD5(s)`	MD5 hash as hex string	`MD5('hello')`
`SHA1(s)`	SHA-1 hash as hex string	`SHA1('hello')`
`SHA256(s)`	SHA-256 hash as hex string	`SHA256('hello')`
`SHA512(s)`	SHA-512 hash as hex string	`SHA512('hello')`

Date / Time

Function	Description	Example
`NOW()` / `CURRENT_TIMESTAMP`	Current UTC timestamp	`NOW()`
`CURRENT_DATE`	Current UTC date (midnight)	`CURRENT_DATE`
`CURRENT_TIME`	Current time as `HH:MM:SS`	`CURRENT_TIME`
`DATE(expr)`	Truncate a timestamp to the date part	`DATE(NOW())` — `'2025-04-10'`
`EXTRACT(field FROM ts)`	Extract part of timestamp	`EXTRACT(YEAR FROM created_at)`
`DATE_TRUNC(unit, ts)`	Truncate to specified precision	`DATE_TRUNC('hour', created_at)`
`DATE_FORMAT(ts, fmt)`	Format timestamp (MySQL-style)	`DATE_FORMAT(NOW(), '%Y-%m-%d')`
`TO_CHAR(ts, fmt)`	Format timestamp with PostgreSQL format codes	`TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')`
`TO_DATE(str, fmt)`	Parse a date string using a format pattern	`TO_DATE('2025-04-10', 'YYYY-MM-DD')`
`TIME_BUCKET(interval_secs, ts)`	Bucket timestamps (time-series)	`TIME_BUCKET(300, event_time)`

Supported EXTRACT fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, ISODOW, MILLISECOND.

Conditional

Function	Description	Example
`COALESCE(a, b, ...)`	First non-null argument	`COALESCE(nickname, first_name, 'Anonymous')`
`NULLIF(a, b)`	Returns NULL if `a = b`, else `a`	`NULLIF(discount, 0)`
`GREATEST(a, b, ...)`	Largest argument	`GREATEST(score1, score2, score3)`
`LEAST(a, b, ...)`	Smallest argument	`LEAST(price, max_price)`
`CASE WHEN ... THEN ... END`	Conditional expression	`CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END`

Type Conversion

Function	Description	Example
`CAST(expr AS type)`	Convert to target type (error on failure)	`CAST('42' AS INT64)`
`TRY_CAST(expr AS type)`	Convert to target type (NULL on failure)	`TRY_CAST('abc' AS INT64)` — `NULL`
`expr::type`	PostgreSQL-style cast shorthand	`'42'::INT64`

Utility

Function	Description	Example
`GEN_RANDOM_UUID()`	Generate a v4 UUID string	`GEN_RANDOM_UUID()`
`TYPEOF(expr)` / `PG_TYPEOF`	Return the data type name of an expression	`TYPEOF(42)` — `'INT64'`
`IF(cond, then, else)` / `IIF`	Inline conditional (shorthand for CASE)	`IF(x > 0, 'pos', 'non-pos')`
`SESSION_USER`	Current session user name	`SELECT SESSION_USER`

Vector Distance

Function	Description	Example
`L2_DISTANCE(a, b)`	Euclidean distance between two vectors	`L2_DISTANCE(embedding, VECTOR('[0.1, 0.2]'))`
`COSINE_SIMILARITY(a, b)`	Cosine similarity (1.0 = identical)	`COSINE_SIMILARITY(embedding, query_vec)`
`INNER_PRODUCT(a, b)` / `DOT_PRODUCT`	Dot product of two vectors	`INNER_PRODUCT(embedding, query_vec)`

JSON

Function	Description	Example
`JSON_EXTRACT(json, path)`	Extract JSON element (via `->`)	`JSON_EXTRACT(data, 'name')`
`JSON_EXTRACT_SCALAR(json, path)`	Extract JSON element as text (via `->>`)	`JSON_EXTRACT_SCALAR(data, 'email')`
`JSON_PATH(json, path)` / `JSON_PATH_QUERY`	Extract element using JSONPath expression (returns JSON)	`JSON_PATH(data, '$.address.city')`
`JSON_PATH_SCALAR(json, path)` / `JSON_PATH_QUERY_SCALAR`	Extract element using JSONPath expression (returns text)	`JSON_PATH_SCALAR(data, '$.name')`

Aggregate Functions

Function	Description	Example
`COUNT(*)`	Count all rows	`SELECT COUNT(*) FROM orders`
`COUNT(expr)`	Count non-null values	`COUNT(email)`
`COUNT(DISTINCT expr)`	Count distinct non-null values	`COUNT(DISTINCT customer_id)`
`SUM(expr)`	Sum of values	`SUM(amount)`
`AVG(expr)`	Arithmetic mean	`AVG(price)`
`MIN(expr)`	Minimum value	`MIN(created_at)`
`MAX(expr)`	Maximum value	`MAX(score)`
`MEDIAN(expr)`	Median value	`MEDIAN(response_time)`
`MODE(expr)`	Most frequent value	`MODE(category)`
`STDDEV(expr)` / `STDDEV_POP`	Population standard deviation	`STDDEV(latency)`
`STDDEV_SAMP(expr)`	Sample standard deviation	`STDDEV_SAMP(latency)`
`VAR_POP(expr)` / `VARIANCE`	Population variance	`VAR_POP(score)`
`VAR_SAMP(expr)`	Sample variance	`VAR_SAMP(score)`
`STRING_AGG(expr, sep)`	Concatenate strings with separator	`STRING_AGG(tag, ', ')`
`GROUP_CONCAT(expr)`	Concatenate strings (MySQL compat)	`GROUP_CONCAT(name)`
`ARRAY_AGG(expr)` / `JSON_ARRAYAGG` / `JSONB_AGG` / `JSON_AGG`	Collect values into an array	`ARRAY_AGG(product_id)`
`BOOL_AND(expr)`	True if all values are true	`BOOL_AND(is_verified)`
`BOOL_OR(expr)`	True if any value is true	`BOOL_OR(has_error)`
`BIT_AND(expr)`	Bitwise AND across all values in group	`BIT_AND(flags)`
`BIT_OR(expr)`	Bitwise OR across all values in group	`BIT_OR(flags)`
`BIT_XOR(expr)`	Bitwise XOR across all values in group	`BIT_XOR(flags)`
`ANY_VALUE(expr)`	Arbitrary non-null value from group	`ANY_VALUE(description)`
`FIRST(val, ts)`	Value with the earliest timestamp	`FIRST(status, updated_at)`
`LAST(val, ts)`	Value with the latest timestamp	`LAST(status, updated_at)`
`ARG_MIN(val, key)` / `MIN_BY` / `ARGMIN`	Value where key is minimum	`ARG_MIN(name, price)`
`ARG_MAX(val, key)` / `MAX_BY` / `ARGMAX`	Value where key is maximum	`ARG_MAX(name, score)`
`PERCENTILE_CONT(pct)`	Continuous percentile	`PERCENTILE_CONT(0.95)`
`PERCENTILE_DISC(pct)`	Discrete percentile	`PERCENTILE_DISC(0.5)`
`APPROX_COUNT_DISTINCT(expr)`	Approximate distinct count (HyperLogLog)	`APPROX_COUNT_DISTINCT(user_id)`

All aggregates support the FILTER (WHERE ...) modifier:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped
FROM orders;

See Aggregations for detailed reference.

Window Functions

Function	Description
`ROW_NUMBER()`	Sequential row number within partition
`RANK()`	Rank with gaps for ties
`DENSE_RANK()`	Rank without gaps for ties
`NTILE(n)`	Divide rows into `n` buckets
`PERCENT_RANK()`	Relative rank (0.0 to 1.0)
`CUME_DIST()`	Cumulative distribution
`LAG(expr [, offset [, default]])`	Value from preceding row
`LEAD(expr [, offset [, default]])`	Value from following row
`FIRST_VALUE(expr)`	First value in the window frame
`LAST_VALUE(expr)`	Last value in the window frame
`NTH_VALUE(expr, n)`	Nth value in the window frame
`SUM(expr) OVER (...)`	Running or partitioned sum
`AVG(expr) OVER (...)`	Running or partitioned average
`COUNT(expr) OVER (...)`	Running or partitioned count
`MIN(expr) OVER (...)`	Running or partitioned minimum
`MAX(expr) OVER (...)`	Running or partitioned maximum
`MOVING_AVG(expr [, window_size])`	Moving average over a sliding window
`MOVING_SUM(expr [, window_size])`	Moving sum over a sliding window
`EXP_MOVING_AVG(expr [, alpha])`	Exponential moving average
`DELTA(expr)`	Difference from the previous row
`RATE(expr)`	Rate of change from the previous row

SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

See SELECT for window function syntax details.

Table-Valued Functions

Function	Description	Example
`GENERATE_SERIES(start, stop [, step])`	Generate a sequence of values	`SELECT * FROM GENERATE_SERIES(1, 10)`
`UNNEST(array)`	Expand an array into rows	`SELECT * FROM UNNEST(ARRAY[1, 2, 3])`

Extended SQL Features

ParticleDB supports several SQL extensions beyond the standard dialect for improved ergonomics and analytical power.

GROUP BY ALL

Automatically infers which select-list columns belong in the GROUP BY — any column not inside an aggregate function is included automatically.

-- Standard GROUP BY (explicit)
SELECT category, region, SUM(amount) FROM orders GROUP BY category, region;

-- GROUP BY ALL (auto-inferred — equivalent)
SELECT category, region, SUM(amount) FROM orders GROUP BY ALL;

-- Works with multiple aggregates and ORDER BY
SELECT a, b, COUNT(*), AVG(c) FROM t GROUP BY ALL ORDER BY COUNT(*) DESC;

SELECT * EXCLUDE

Return all columns except the named ones. Useful for wide tables where you want to drop sensitive or internal columns without listing every remaining column.

-- Drop two sensitive columns
SELECT * EXCLUDE(password, ssn) FROM users;

-- Combine with WHERE
SELECT * EXCLUDE(id) FROM orders WHERE amount > 100;

-- Works on subquery results
SELECT * EXCLUDE(rn) FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM students
) sub;

QUALIFY

Filter rows based on the result of a window function — analogous to HAVING for aggregates, but for window functions. Avoids a subquery wrap.

-- Top-3 employees per department by salary
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM emp
QUALIFY rn <= 3;

-- Students tied for first place
SELECT *, RANK() OVER (ORDER BY score DESC) AS r
FROM students
QUALIFY r = 1;

CONTAINS

CONTAINS(string, substring) returns TRUE if string contains substring. A readable alternative to LIKE '%substr%'.

-- Filter gmail users
SELECT * FROM users WHERE CONTAINS(email, '@gmail.com');

-- Compose multiple checks
SELECT CONTAINS('nested query', 'query') AND CONTAINS('test', 'es');

Trailing Commas in SELECT

A trailing comma after the last item in a SELECT list is silently accepted.

SELECT
    name,
    email,
    created_at,   -- trailing comma is fine
FROM users
WHERE active = TRUE;

PK Uniqueness Enforcement

INSERT into a table with a primary key now returns an error if a row with the same PK already exists, matching the SQL standard. Use ON CONFLICT to upsert.

CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'alice');
INSERT INTO users VALUES (1, 'bob');  -- ERROR: duplicate key

-- Upsert instead
INSERT INTO users VALUES (1, 'bob')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

New Functions (this release)

Function	Category	Description
`PI()`	Math	Returns pi (3.14159…)
`LOG(base, x)`	Math	Two-argument form: log base `base` of `x`
`TRUNC(x [, n])`	Math	Truncate to `n` decimal places (no rounding)
`FLOOR(x)`	Math	Parser support added
`CEIL(x)` / `CEILING(x)`	Math	Parser support added
`BIT_AND(expr)`	Aggregate	Bitwise AND over group
`BIT_OR(expr)`	Aggregate	Bitwise OR over group
`BIT_XOR(expr)`	Aggregate	Bitwise XOR over group
`TO_CHAR(ts, fmt)`	Date	PostgreSQL format codes: `YYYY`, `MM`, `DD`, `HH24`, `MI`, `SS`
`TO_DATE(str, fmt)`	Date	Parse a date string into a timestamp
`DATE(expr)`	Date	Truncate timestamp to date (midnight UTC)
`DATE_FORMAT(ts, fmt)`	Date	MySQL-style alias for `TO_CHAR`
`CURRENT_TIME`	Date	Current time as `HH:MM:SS`
`SESSION_USER`	System	Current session user name
`EXTRACT(QUARTER ...)`	Date	Quarter of the year (1-4)
`EXTRACT(ISODOW ...)`	Date	ISO day of week (1=Monday, 7=Sunday)
`EXTRACT(WEEK ...)`	Date	ISO week number (1-53)
`EXTRACT(MILLISECOND ...)`	Date	Milliseconds component
`SPLIT_PART(s, delim, n)`	String	Split and return the nth token
`LPAD(s, len)`	String	`fill` now optional (defaults to space)
`RPAD(s, len)`	String	`fill` now optional (defaults to space)
`CONTAINS(s, sub)`	String	Boolean substring membership check
`CROSS JOIN`	Join	Explicit cross-join syntax fully supported

-- Math
SELECT PI();                          -- 3.141592653589793
SELECT LOG(2, 1024);                  -- 10.0
SELECT TRUNC(3.14159, 3);            -- 3.141
SELECT FLOOR(2.7), CEIL(2.3);        -- 2.0, 3.0

-- Bitwise aggregates
SELECT role, BIT_AND(flags), BIT_OR(flags), BIT_XOR(flags)
FROM permissions
GROUP BY role;

-- Date formatting
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_DATE('2025-04-10', 'YYYY-MM-DD');
SELECT CURRENT_TIME;                   -- e.g. '14:32:07'

-- Extended EXTRACT fields
SELECT
    EXTRACT(QUARTER FROM NOW()),       -- 1-4
    EXTRACT(ISODOW FROM NOW()),        -- 1=Mon ... 7=Sun
    EXTRACT(WEEK FROM NOW());          -- ISO week 1-53

-- String helpers
SELECT SPLIT_PART('a.b.c', '.', 2);   -- 'b'
SELECT LPAD('42', 5);                  -- '   42'
SELECT RPAD('hi', 5);                  -- 'hi   '

SQL Reference

Statements

Data Definition (DDL)

Data Manipulation (DML)

Transactions

Access Control

Utility

Data Types

Operators

Comparison

Logical

Arithmetic

String

Predicates

JSON

Vector

Scalar Functions

Math

String

Date / Time

Conditional

Type Conversion

Utility

Vector Distance

JSON

Aggregate Functions

Window Functions

Table-Valued Functions

Extended SQL Features

GROUP BY ALL

SELECT * EXCLUDE

QUALIFY

CONTAINS

Trailing Commas in SELECT

PK Uniqueness Enforcement

New Functions (this release)

Further Reading