SQL statements

This page lists every statement a Pondra node runs, grouped by what it changes. Queries use Apache DataFusion’s SQL, which is close to PostgreSQL. Use the page to look up a statement’s form; the guides show how to use them together.

A node takes SQL on every door: POST /sql, the shell, pondra sql, Postgres, Flight SQL, MCP and the Python and JavaScript clients. One request may hold several statements separated by ;. They run in order, and the answer is the last one’s: rows for a query, a small JSON outcome such as {"rows": 2} for a write.

Types

Columns take these types. A list is written type[], such as FLOAT[] for an embedding.

Type	Also written	Holds
TINYINT, SMALLINT, INT, BIGINT		8, 16, 32 and 64-bit integers
REAL, DOUBLE		32 and 64-bit floating point
DECIMAL(p, s)		exact numbers; 0.07 in SQL is a decimal
VARCHAR	TEXT, STRING	text
BOOLEAN		true or false
DATE, TIME, TIMESTAMP		TIMESTAMP is a wall-clock time with no zone, kept in microseconds
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	an instant, kept and shown in UTC with its zone: 2026-09-29T05:00:00Z
BYTEA	BINARY, VARBINARY, BLOB	bytes
JSON	VARIANT	JSON text, read with json_get and -> (see functions)
type[]		a list of one type
an enum type, or ENUM('a', 'b')		text, one of its labels (enum types)

A TIMESTAMPTZ written with an offset ('2026-09-29 10:00:00+05') is stored as the instant it names, so it reads back as 2026-09-29T05:00:00Z. A time may leave out its seconds, as in Postgres and DuckDB: '2024-05-01 10:30' is a timestamp in INSERT, CAST, TIMESTAMP '…' and comparisons. to_timestamp(…) answers a TIMESTAMP, not a TIMESTAMPTZ: see dates and times.

A value that doesn’t cast to its column’s type is refused, and the error names the value: 'abc' into a BIGINT is an error, never a NULL. substr, substring(b FROM 2 FOR 1) and byte_length work on bytes as they do on text. A CAST to bytes takes BYTEA only.

Enum types

Statement	Syntax
Make one	CREATE TYPE [IF NOT EXISTS] name AS ENUM ('label', …)
Add a label	ALTER TYPE name ADD VALUE [IF NOT EXISTS] 'label' [BEFORE | AFTER 'label']
Rename	ALTER TYPE name RENAME TO new_name
Drop	DROP TYPE [IF EXISTS] name [, …]

A column of an enum type holds text: other engines and every client read it as a string. Every way of writing rows (INSERT, UPDATE, MERGE, COPY, appends, Kafka, Flight, pondra sql) refuses a value its labels don’t list, with Postgres’s code 22P02; NULL is allowed. A column may also say its labels itself, as MySQL and DuckDB write it: size ENUM('s', 'm', 'l'). 'happy'::mood is a label checked as it is read, and enum_range(NULL::mood), enum_first and enum_last give the labels in their order. Values compare and sort as text; array_position(enum_range(NULL::mood), feeling) is their place. A label is never renamed or taken away (the rows keep the text they were written with), a type isn’t dropped while a column uses it (2BP01), and a temporary table’s columns take no enum. A type is listed in pondra.objects, shown by SHOW CREATE TYPE and described by COMMENT ON TYPE; over Postgres its columns are text.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE moods (who VARCHAR, feeling mood);
INSERT INTO moods VALUES ('ann', 'happy'), ('bo', 'sad');
ALTER TYPE mood ADD VALUE 'meh' BEFORE 'ok';
SELECT who, feeling, array_position(enum_range(NULL::mood), feeling) AS place FROM moods ORDER BY place;

 who | feeling | place
-----+---------+-------
 bo  | sad     |     1
 ann | happy   |     4

Queries

Form	Meaning
SELECT … FROM t	any DataFusion query: joins, WITH, windows, VALUES, sub-queries
FROM t [SELECT …] [WHERE …] [GROUP BY …] [ORDER BY …] [LIMIT n]	DuckDB’s form: the FROM first, then any clauses
$name	a variable or a parameter, bound by the node and never pasted into the text
FROM t AT (VERSION => n), AT (TIMESTAMP => '…'), AT (OFFSET => -3600)	the table as it was after commit n, at that time, or that many seconds ago (below)
FROM t VERSION AS OF n, FROM t TIMESTAMP AS OF '…', FROM t FOR SYSTEM_TIME AS OF '…'	the same, in Delta’s, Spark’s and SQL:2011’s words: what follows is a number, a string, TIMESTAMP '…', a variable or (an expression)
FROM a ASOF JOIN b MATCH_CONDITION (a.ts >= b.ts) ON a.k = b.k	each row gets the other table’s row as it was at that moment (>=, >, <=, <)
FROM a ASOF [LEFT] JOIN b ON a.k = b.k AND a.ts >= b.ts	DuckDB’s spelling: only rows with a match, or all of a’s with LEFT
PIVOT t ON col [IN (…)] USING agg(x) [GROUP BY …], … FROM t PIVOT (agg(x) FOR col IN (…))	a column per value of col; without IN, the values are found when it runs
UNPIVOT t ON a, b INTO NAME n VALUE v, … FROM t UNPIVOT [INCLUDE NULLS] (v FOR n IN (a, b))	a row per column, NULLs left out unless included
COLUMNS('regex'), COLUMNS(*), COLUMNS(['a', 'b'])	the columns it picks, each named after its column (min(COLUMNS(*))); in a WHERE, each one’s condition, ANDed
SELECT * RENAME (a AS b), * EXCLUDE (…), * REPLACE (… AS …)	the columns * gives, renamed, left out or replaced
ORDER BY ALL [DESC], GROUP BY ALL	by every column of the answer, left to right; by every column not aggregated
FETCH FIRST n ROWS ONLY, OFFSET n ROWS FETCH NEXT m ROWS ONLY	the standard’s LIMIT
[x * 2 FOR x IN l IF x > 0], list_transform(l, x -> x + 1), LAMBDA x: x + 1	list comprehensions and lambdas (list_filter, array_any_match and the others take them too)
max_by(x, y), arg_max, min_by, arg_min, list(x), string_split(s, ',')	DuckDB’s names (max_by leaves out rows whose y is NULL)
json_extract(j, '$.a.b[0]'), json_extract_string(…), x::JSON	JSON as text: a path written as a literal
SELECT x * 2 AS y FROM t WHERE y > 1	a select’s alias in its WHERE (a column of that name wins)
SUMMARIZE t, SUMMARIZE SELECT …	a row per column: its type, min and max, distinct values about, mean, spread, quartiles, count and NULLs
FROM t USING SAMPLE 10% (or n ROWS), FROM t TABLESAMPLE (10) (or (n ROWS))	a sample: each row kept on its own, or n of them
EXPLAIN SELECT …, EXPLAIN ANALYZE SELECT …	the estimated plan, each step with the rows it expects (expected_rows), and whether it would spread over the nodes; run, the actual plan: each step’s rows (output_rows) and time beside what it expected
DESCRIBE t, SHOW TABLES, SHOW COLUMNS FROM t	what the lake holds
SHOW VIEWS, SHOW MATERIALIZED VIEWS [LIKE 'p%'], SELECT … FROM pondra.tables	each table and view with its kind (see system tables)
SHOW SCHEMAS, SHOW DATABASES, SHOW SECRETS, SHOW USERS, SHOW ROLES, SHOW GRANTS (each [LIKE 'p%'])	the schemas, the lakes a query can name, the secrets (never their values), users, roles and grants
SELECT … FROM 'file.parquet'	a file or URL as a table (see names)
SELECT … FROM spark_sql('SELECT …')	a query in Spark SQL’s grammar, with Spark’s functions (PySpark’s spark.sql sends this)

CREATE TABLE orders (id BIGINT, item VARCHAR, qty INT, amount DOUBLE);
INSERT INTO orders VALUES (1, 'tea', 2, 7.0), (2, 'cake', 1, 4.5), (3, 'tea', 1, 3.5);
CREATE TABLE trades (ts TIMESTAMP, sym VARCHAR, px DOUBLE);
CREATE TABLE quotes (ts TIMESTAMP, sym VARCHAR, bid DOUBLE);
INSERT INTO trades VALUES (TIMESTAMP '2026-09-28 10:00:05', 'ABC', 10.1);
INSERT INTO quotes VALUES (TIMESTAMP '2026-09-28 10:00:00', 'ABC', 10.0), (TIMESTAMP '2026-09-28 10:00:09', 'ABC', 10.2);
FROM orders SELECT item, sum(amount) AS total GROUP BY item ORDER BY total DESC;
FROM orders WHERE item = 'tea' ORDER BY id;
SELECT t.ts, t.px, q.bid FROM trades t ASOF JOIN quotes q MATCH_CONDITION (t.ts >= q.ts) ON t.sym = q.sym;
SELECT t.ts, t.px, q.bid FROM trades t ASOF JOIN quotes q ON t.sym = q.sym AND t.ts >= q.ts;
PIVOT orders ON item USING sum(amount);
SELECT max(COLUMNS('qty|amount')), max_by(item, amount) AS biggest FROM orders ORDER BY ALL;
SUMMARIZE orders;
SHOW TABLES;

An answer’s columns are named as other engines name them, where DataFusion alone would refuse the query:

• a column cast without a name (ts::date, CAST(ts AS DATE)) is named as the column, ts, as Postgres names it; when the SELECT has another column that may be named so (a *, or the column itself), it is named as written, ts::DATE, as Snowflake and DuckDB name every cast;
• a column the SELECT has twice (SELECT id, *, SELECT id, id) is named id_1 where it is named in the SELECT, as DuckDB names the second in a frame. Name it yourself with AS for another name.

SELECT id::VARCHAR, * FROM orders WHERE id = 1;
SELECT id, * FROM orders WHERE id = 1;

[{"id::VARCHAR":"1","id":1,"item":"tea","qty":2,"amount":7.0}]
[{"id_1":1,"id":1,"item":"tea","qty":2,"amount":7.0}]

A parameter goes with the request, in JSON. The Python and JavaScript clients do this for you:

Python

import pondra
db = pondra.connect("http://localhost:8080")
print(db.sql("SELECT item, qty FROM orders WHERE amount > $min ORDER BY id", min=4).rows())

JavaScript

import { connect } from "pondra";
const db = connect("http://localhost:8080");
console.log(await db.sql("SELECT item, qty FROM orders WHERE amount > $min ORDER BY id", { min: 4 }));

A table’s past

t AT (…) reads an append table as it was: after commit n (VERSION => n, a row’s _version), at a time (TIMESTAMP => '2026-10-02 12:00:00') or some seconds ago (OFFSET => -3600). It goes wherever a table does: a join (the same table at two moments too), a view, CREATE TABLE … AS, INSERT … SELECT. A table’s past is kept for its retention, a day unless set; a moment before that is refused by name, and so is a keyed table, which keeps only each key’s newest row.

CREATE TABLE prices (item VARCHAR, price DOUBLE);
INSERT INTO prices VALUES ('tea', 3.5), ('cake', 4.0);
UPDATE prices SET price = 3.8 WHERE item = 'tea';
SELECT item, price, _version FROM prices ORDER BY item;
SELECT item, price FROM prices AT (OFFSET => -3600);
SELECT n.item, w.price AS was, n.price AS now FROM prices n JOIN prices AT (OFFSET => -1) w ON n.item = w.item;
CREATE TABLE prices_test CLONE prices;
RESTORE TABLE prices TO TIMESTAMP AS OF '2026-10-02 12:00:00';

RESTORE TABLE t TO VERSION AS OF n (or TIMESTAMP AS OF '…') puts an append table back as it was then, in one change: rows changed since get their old values (and keep their _row_id), rows made since go, rows deleted since come back. It is a change like any other, so another RESTORE undoes it.

CREATE TABLE c CLONE t (or SHALLOW CLONE) makes a table of t’s rows as they are, copying no file: c lists t’s files where they are, and writes its own beside them. The two change apart from then on, and neither’s merges, expiry or DROP deletes a file the other still reads. A clone isn’t published for other engines (CREATE TABLE c AS SELECT * FROM t makes a copy that can be), and its past starts when it was made.

Tables

Statement	Syntax
Create	CREATE [OR REPLACE] TABLE [IF NOT EXISTS] t (column type [NOT NULL] [DEFAULT expression] [CHECK (condition)] [PRIMARY KEY] [UNIQUE] [REFERENCES u (column) NOT ENFORCED] [MERGE sum|count|min|max], … [, PRIMARY KEY (column, …)] [, [CONSTRAINT name] CHECK (condition) | UNIQUE (column, …) | FOREIGN KEY (column, …) REFERENCES u (column, …) NOT ENFORCED]) [PARTITION BY expression] [CLUSTER BY (column, …)] [SEQUENCE BY column] [TTL column + INTERVAL '…'] [WITH (option = value, …)]: the layout clauses in any order (table options)
Create like another	CREATE TABLE t LIKE s: an empty table with s’s columns and their types, as Postgres’s does
Create from a query	CREATE TABLE t AS SELECT …, CREATE TABLE t (a INT, b VARCHAR) AS VALUES …, or SELECT … INTO t FROM …
Add a column	ALTER TABLE t ADD COLUMN [IF NOT EXISTS] c type
Rename a column	ALTER TABLE t RENAME COLUMN a TO b
Rename a table	ALTER TABLE t RENAME TO u
Drop a column	ALTER TABLE t DROP COLUMN [IF EXISTS] c
Widen a column	ALTER TABLE t ALTER COLUMN c TYPE BIGINT
Add a constraint	ALTER TABLE t ADD [CONSTRAINT name] UNIQUE (column, …) | CHECK (condition) | FOREIGN KEY (…) REFERENCES u (…) NOT ENFORCED
Drop a constraint	ALTER TABLE t DROP CONSTRAINT [IF EXISTS] name
Change the layout	ALTER TABLE t CLUSTER BY (user), ALTER TABLE t TTL ts + INTERVAL '2 hours', ALTER TABLE t SEQUENCE BY ts
Change options	ALTER TABLE t SET (publish = delta, retention = '7 days')
Empty	TRUNCATE [TABLE] t
Drop	DROP TABLE [IF EXISTS] t [, t2 …] [PURGE]
Bring back a dropped table	UNDROP TABLE t
Copy a table without copying files	CREATE TABLE [IF NOT EXISTS] c [SHALLOW] CLONE t
Put a table back as it was	RESTORE TABLE t TO VERSION AS OF n, or TO TIMESTAMP AS OF '…'

A table of that name already there refuses CREATE TABLE (42P07, as in Postgres), whether it has the same columns or not; IF NOT EXISTS leaves it as it is, and OR REPLACE drops it and makes it anew. So does every kind of object: tables, views, materialized views, functions, procedures, macros, tasks, secrets, external tables and a session’s temporary tables, views and secrets take both words (OR REPLACE and IF NOT EXISTS together are refused, as one contradicts the other). Schemas, databases, users and roles take IF NOT EXISTS only: replacing one would drop what it holds or the rights given to it.

PARTITION BY day(ts) and CLUSTER BY (user, url) are the partition_by and cluster_by options in BigQuery’s, Snowflake’s and Databricks’ words. PARTITIONED BY (c) (Spark’s), days(ts) (Iceberg’s), DATE(ts) and TIMESTAMP_TRUNC(ts, DAY) (BigQuery’s) and date_trunc('day', ts) are the same partitions. A clause and the option it stands for, both given, are refused.

A table without a primary key is an append table. With a key, a new row replaces the old one with the same key. The options are on table options. An ALTER TABLE rewrites no file: old rows read a new column as null.

NOT NULL and DEFAULT are enforced on every door: INSERT, POST /append, COPY, Flight, Kafka, a bulk INSERT … SELECT and pondra sql. A key’s columns are NOT NULL too; a table made by an older release keeps its old rules. A row that leaves a column out gets its default, each row its own (DEFAULT now() is the time of each row). A JSON row sent to /append or Kafka without the column takes the default; an explicit null stays null, and is refused if the column is NOT NULL. A DEFAULT that doesn’t cast to the column’s type is refused at CREATE.

A CHECK is enforced on the same doors, and on UPDATE and MERGE: a row for which its condition is false is refused with 23514 (new row for relation "t" violates check constraint "t_balance_check"); NULL passes, as in Postgres. One without a name is named as Postgres names it: {table}_{column}_check on a column, {table}_check on the table.

A UNIQUE column, or columns, never holds a value twice: an INSERT, INSERT … SELECT, UPDATE, MERGE or transaction that would give one a second time is refused with 23505 (duplicate key value violates unique constraint "users_email_key"), from any node, over Postgres and from pondra sql. The leader checks every write to the table under the lake’s lock, so two writers on two nodes can never both add one value. A statement is checked as a whole, as the SQL standard has it: an UPDATE that swaps two values holds. NULLs are distinct, as by default in Postgres. In a table with a key, a key’s new version may keep its own value. A UNIQUE table takes its rows through SQL: POST /append, COPY … FROM STDIN, Kafka, Flight and other engines’ commits skip the leader’s check, so they are refused for it, by name. ALTER TABLE … ADD CONSTRAINT … UNIQUE checks the rows already there (23505 if two share a value, and nothing is kept). One without a name is named as Postgres names it: {table}_{columns}_key.

NOT ENFORCED keeps a constraint as a fact about the data, for the tools that read it (information_schema, pg_constraint, BI tools’ joins, SHOW CREATE), and checks nothing: a PRIMARY KEY … NOT ENFORCED makes no key, so the table takes rows as they come. A FOREIGN KEY must say NOT ENFORCED, since nothing checks a reference yet. A renamed column keeps its constraints; a dropped one takes them with it.

CREATE TABLE members (id BIGINT, email VARCHAR UNIQUE, team BIGINT REFERENCES teams (id) NOT ENFORCED);
ALTER TABLE members ADD CONSTRAINT one_handle UNIQUE (team, id);

ALTER TABLE … ADD COLUMN … NOT NULL and … DEFAULT are refused, because the rows already there would need a value and no file is rewritten. Add the column, then UPDATE it.

RENAME TO changes the name in place: no file is rewritten, rows still in the log stay the table’s, and its Delta and Iceberg copies stay readable. A stored view reads a table by name, so after a rename it reads whatever takes the old name next. It is refused when the new name is taken, when a materialized view or a task follows the table, and for a materialized view.

A dropped table is kept for a day, or for its retention, and UNDROP TABLE t brings it back as it was: every row, with its ids and versions. pondra.dropped lists the tables that can still come back (see system tables). UNDROP takes the table dropped last under that name, and is refused while another table has the name: rename that one first. DROP TABLE … PURGE, or retention = '0 seconds', keeps nothing.

CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR, plan VARCHAR);
CREATE TABLE clicks (ts TIMESTAMP, user VARCHAR, url VARCHAR) PARTITION BY day(ts) CLUSTER BY (user);
CREATE TABLE big_orders AS SELECT * FROM orders WHERE amount > 4;
CREATE TABLE accounts (id BIGINT PRIMARY KEY, email VARCHAR NOT NULL, plan VARCHAR DEFAULT 'free', made TIMESTAMP DEFAULT now());
INSERT INTO accounts (id, email) VALUES (1, 'ann@example.com');
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users RENAME COLUMN age TO years;
ALTER TABLE users ALTER COLUMN years TYPE BIGINT;
ALTER TABLE users DROP COLUMN years;
ALTER TABLE clicks SET (publish = 'delta');
ALTER TABLE big_orders RENAME TO large_orders;
DROP TABLE large_orders;
UNDROP TABLE large_orders;
DROP TABLE large_orders PURGE;
TRUNCATE accounts;

Sequences and identity columns

Statement	Syntax
Make a sequence	CREATE [OR REPLACE] SEQUENCE [IF NOT EXISTS] s [AS smallint|integer|bigint] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE] [START [WITH] n] [CACHE n] [[NO] CYCLE], the options in any order
Its next value	nextval('s')
The last value this session took	currval('s')
Set it	setval('s', n): the next value is the one after n; setval('s', n, false): it is n
Change it	ALTER SEQUENCE [IF EXISTS] s RESTART [WITH n], … INCREMENT BY n (or any option above), … RENAME TO t
Drop	DROP SEQUENCE [IF EXISTS] s [, …]
A column numbered for you	id BIGINT GENERATED ALWAYS AS IDENTITY, … GENERATED BY DEFAULT AS IDENTITY (START WITH 100), SERIAL, BIGSERIAL, SMALLSERIAL, AUTO_INCREMENT, IDENTITY(1, 1), AUTOINCREMENT

A sequence hands out each value once, across the whole cluster and through a leader’s failover. Each node takes a block of values at a time and gives them out in order, so values from one node count up and the nodes’ values interleave. A node that stops, and any change to the sequence, leave a gap, as Postgres’s CACHE does; CACHE n is the smallest block a node takes. Past its MAXVALUE (or MINVALUE, counting down) a sequence stops with 2200H, unless it CYCLEs.

An identity column is a sequence its column owns, named {table}_{column}_seq: a row that leaves the column out takes the next value. ALWAYS refuses a value given in an INSERT or set by an UPDATE (428C9); BY DEFAULT, SERIAL and the others take one. Loading rows (POST /append, COPY, Kafka, Flight) keeps a value given, as Postgres’s COPY does. The sequence goes with DROP TABLE … PURGE and comes back with UNDROP.

currval is a session’s: a Postgres connection’s, or a client’s (x-pondra-session), and 55000 before that session’s first nextval. A DEFAULT nextval('s') needs s to be there, and s can’t be dropped or renamed while a default names it. A materialized view can’t call nextval: its rows are worked out again as its tables change, and would be numbered again.

CREATE SEQUENCE invoice_no START 1000 INCREMENT 10;
SELECT nextval('invoice_no') AS n;
CREATE TABLE tickets (id BIGINT GENERATED ALWAYS AS IDENTITY, title VARCHAR);
INSERT INTO tickets (title) VALUES ('printer jam'), ('no coffee');
SELECT * FROM tickets ORDER BY id;
CREATE TABLE invoices (no BIGINT DEFAULT nextval('invoice_no'), amount DECIMAL(10, 2));
INSERT INTO invoices (amount) VALUES (12.50);

 id | title
----+-------------
  1 | printer jam
  2 | no coffee

Indexes

Statement	Syntax
Make one	CREATE INDEX [IF NOT EXISTS] [name] ON t [USING method] (column | (expression) [ASC | DESC] [NULLS FIRST | LAST], …) [INCLUDE (column, …)] [WHERE condition]
Rename	ALTER INDEX [IF EXISTS] name RENAME TO new_name
Drop	DROP INDEX [IF EXISTS] name [, …]

An index is an object like a table’s others: listed in pondra.objects and pg_indexes, shown by SHOW CREATE INDEX, described by COMMENT ON INDEX. Nothing is built for it, and a notice says so: every file keeps its columns’ ranges, which a filter skips files and row groups by, and CLUSTER BY orders a table’s rows to keep those ranges narrow. So a schema copied from Postgres, an ORM’s migrations or dbt’s indexes run as they are. An index is in its table’s schema, shares names with tables, views and sequences, follows a renamed table or column, and goes with its table or a column it names. Left without a name it is called {table}_{columns}_idx. Vector and text indexes (USING hnsw, ivfflat, bm25) are refused by name. CREATE UNIQUE INDEX [IF NOT EXISTS] [name] ON t (column, …), as ORMs write a unique column (Prisma’s @unique, Rails’ unique: true), is the table’s UNIQUE constraint under that name, checked as one (Tables), and DROP INDEX name drops it; on an expression, with WHERE or INCLUDE, it is refused.

CREATE INDEX ON tickets (title);
SELECT name, definition FROM pondra.objects WHERE kind = 'index';

       name       |                    definition
------------------+---------------------------------------------------
 tickets_title_idx | CREATE INDEX tickets_title_idx ON tickets (title)

Rows

Statement	Syntax
Insert	INSERT INTO t [(column, …)] VALUES (…), … or INSERT INTO t [(column, …)] SELECT …
Insert, on a key that exists	INSERT … ON CONFLICT (key) DO NOTHING, or … ON CONFLICT (key) DO UPDATE SET column = t.column + excluded.column (Postgres’s)
The same, shorter	INSERT OR IGNORE INTO t … (ON CONFLICT DO NOTHING), INSERT OR REPLACE INTO t … (every column the rows give replaces the row’s), as DuckDB and SQLite write them; MySQL’s INSERT IGNORE too
Update	UPDATE t SET column = expression [, …] [FROM other o] [WHERE condition]
Delete	DELETE FROM t [USING other o] [WHERE condition]
Merge	MERGE INTO t [AS x] USING source [AS s] ON condition WHEN [NOT] MATCHED [BY SOURCE] [AND condition] THEN UPDATE SET … | DELETE | INSERT (…) VALUES (…)

UPDATE, DELETE and MERGE work on every table, keyed or not. Each is one commit from one snapshot: it applies all or nothing, and a job id makes a retry a no-op. A row keeps its _row_id through a change. MERGE answers with the rows updated, deleted and inserted; a target row that matches two source rows is an error. UPDATE … FROM and DELETE … USING join the table to another one, and ON CONFLICT answers as MERGE does. In DO UPDATE SET, t.column is the row that is there and excluded.column is the row you sent. MySQL’s ON DUPLICATE KEY UPDATE is refused by name.

INSERT INTO users VALUES (1, 'ann', 'free'), (2, 'bo', 'pro'), (3, 'cy', 'free');
INSERT INTO users (id, name) SELECT id + 10, 'guest' FROM users;
UPDATE users SET plan = 'pro' WHERE name = 'ann';
DELETE FROM users WHERE name = 'guest';
MERGE INTO users AS t
USING (SELECT 4 AS id, 'di' AS name, 'pro' AS plan UNION ALL SELECT 2, 'bo', 'free') AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET plan = s.plan
WHEN NOT MATCHED THEN INSERT (id, name, plan) VALUES (s.id, s.name, s.plan);
INSERT INTO users VALUES (4, 'dee', 'free') ON CONFLICT (id) DO NOTHING;
CREATE TABLE visits (id BIGINT PRIMARY KEY, n BIGINT);
INSERT INTO visits VALUES (1, 1), (2, 1);
INSERT INTO visits VALUES (2, 1), (3, 1) ON CONFLICT (id) DO UPDATE SET n = visits.n + excluded.n;
CREATE TABLE upgrades (id BIGINT, plan VARCHAR);
INSERT INTO upgrades VALUES (1, 'team'), (3, 'gone');
UPDATE users SET plan = u.plan FROM upgrades u WHERE users.id = u.id AND u.plan <> 'gone';
DELETE FROM users USING upgrades u WHERE users.id = u.id AND u.plan = 'gone';
SELECT * FROM users ORDER BY id;
SELECT * FROM visits ORDER BY id;

Transactions

Statement	Syntax
Begin	BEGIN [TRANSACTION | WORK], START TRANSACTION
Commit	COMMIT, END
Roll back	ROLLBACK, ABORT

The statements between BEGIN and COMMIT are one commit, read their own writes, and see the lake as it was at BEGIN (snapshot isolation). A row another commit changed since then refuses COMMIT with 40001: run it again. See Transactions.

Settings and prepared statements

A session’s own, as in Postgres: a Postgres connection’s, the Python or JavaScript client’s, or one script’s (statements sent together, over HTTP or in the shell).

Statement	Syntax
Set	SET name = value, SET name TO value, SET TIME ZONE '+08:00'
Show	SHOW name, SHOW TIME ZONE, SHOW ALL
Set back	RESET name, RESET ALL
Prepare	PREPARE p [(type, …)] AS statement, with $1, $2, … where the values go
Run	EXECUTE p [(value, …)]
Forget	DEALLOCATE [PREPARE] p, DEALLOCATE ALL

• A datafusion.* name is one of DataFusion’s options (datafusion.execution.time_zone, datafusion.execution.batch_size, datafusion.sql_parser.dialect, …), checked as it is set. The session’s queries are planned with it; they run on its node, and aren’t answered from the results other queries left.
• Any other name without a dot is Postgres’s (search_path, extra_float_digits, application_name, …): kept, and shown by SHOW, since clients set many as they connect.
• datafusion.runtime.* (memory, spill folder, caches) is the node’s, set as it starts (PONDRA_MEMORY_MB), and refused here.
• SET datafusion.sql_parser.dialect = 'spark' (or 'databricks') gives the session Spark’s functions where Spark and DataFusion share a name (Spark’s functions), and 'duckdb' takes DuckDB’s STRUCT(a INT) in CREATE TABLE.
• A prepared statement can be a query or a write. EXECUTE puts its values where $1, $2, … were, each cast to the type PREPARE gave, and runs it as if it were sent.

SET TIME ZONE '+08:00';
SELECT TIMESTAMPTZ '2026-10-01T00:00:00Z' AS t;

t
2026-10-01T08:00:00+08:00

CREATE TABLE people (id INT, name VARCHAR, age INT);
INSERT INTO people VALUES (1, 'ann', 31), (2, 'bob', 25);
PREPARE older (INT) AS SELECT name FROM people WHERE age > $1 ORDER BY name;
EXECUTE older (30);

name
ann

Variables

A variable holds one value for the statements after it: a number, text, a date, bytes, a list or a struct (not yet a map). It is a session’s, as settings are (a Postgres connection’s, a client’s, or one script’s); a procedure and a file run have their own.

Statement	Syntax
Declare	DECLARE $name [type] [= value] (or DEFAULT value): type and value both optional; NULL with neither
Declare a parameter	DECLARE PARAMETER $name [type] [= value]: one a run, a request or the console’s bar may give
Change	$name = value (or SET $name = value)
Use	$name wherever a value goes, or getvariable('name') (DuckDB’s; NULL when there is none)
Forget	RESET $name
List	SELECT * FROM pondra.variables (name, value, type, declared)

• The value is worked out once, when it is set, and bound wherever $name is used, never pasted into the text: $day = current_date - 1 is one day for every statement after it.
• A declared type holds: each value the variable takes is cast to it, and one that can’t be is refused. $name = … needs no SET; other spellings are in Other spellings. SET name without a $ is a setting’s (above).
• A column that uses a variable and has no name is named as written: $day, $day + 1.
• In a file, a DECLARE PARAMETER is a parameter with a default: a run’s value for it takes the default’s place, cast to its type (Run files as jobs). A plain DECLARE is the file’s own variable: a value given for it is refused. Python’s db.vars.day is $day (Python client).

CREATE TABLE daily (day DATE, amount DOUBLE);
INSERT INTO daily VALUES (DATE '2026-09-29', 10), (DATE '2026-09-30', 20);
DECLARE $day DATE = DATE '2026-09-30';
$day = $day - 1;
SELECT $day, sum(amount) AS total FROM daily WHERE day = $day;

$day       | total
2026-09-29 | 10.0

Scripts

A script can decide: branch, loop, handle an error, and stop. It runs the same from the console, a .sql file, POST /sql, psql and every Postgres driver, MCP, the clients’ db.sql, a SQL procedure and a task. Each statement in it runs as it would on its own, so a query still spreads across the nodes and a write still goes to the leader.

Statement	What it does
BEGIN … [EXCEPTION WHEN … THEN …] END	a block: its DECLAREs end with it; handlers catch its errors
IF … THEN … ELSEIF … ELSE … END	branches (ELSIF too)
CASE [$x] WHEN … THEN … ELSE … END CASE	branches by value or by condition
WHILE cond DO … END WHILE	a loop while the condition holds
REPEAT … UNTIL cond END REPEAT · LOOP … END LOOP	a loop until the condition holds · until LEAVE
FOR $r IN (query) DO … END	a loop over a query’s rows: $r.column
FOR r IN (query) PARALLEL n DO … END FOR	the same, up to n passes at once (1 to 64)
ASYNC statement · $h = ASYNC statement	start a statement (a block too) and go on · its handle in $h
AWAIT · AWAIT $h, …	wait for every one started · for these: handles, or runs’ ids from START CALL
LEAVE [label] · ITERATE [label]	leave a loop or block · go on with the next pass (label: WHILE …)
RETURN [value]	stop; the value is the script’s answer (result)
RAISE 'text %', $x · RAISE NOTICE … · RAISE	an error (P0001), each % a value · a notice · the handled error again
PRINT value	a notice: the console’s Messages, psql’s NOTICE, the clients’ notices
ASSERT cond [, 'text']	an error (P0004) unless the condition holds
EXECUTE IMMEDIATE 'sql' [INTO $a, …] [USING x, …]	SQL built as text; USING values bound as $1, $2
CALL p(…) INTO $x	a procedure’s or file’s answer, its first row, into variables
IDENTIFIER(value)	a table’s or column’s name from a value, in any statement

• END closes any block. END IF, END WHILE, END FOR, END LOOP and END CASE say which, and one that names another kind of block is refused, at its line.
• Conditions and values are SQL expressions, EXISTS (SELECT …) and subqueries too.
• A handler names errors by Postgres’s codes or names: '40001', serialization_failure, division_by_zero, OTHERS. Inside it, $error is the message and $sqlstate its code.
• BEGIN; and BEGIN TRANSACTION still start a transaction; BEGIN followed by a statement starts a block.
• A PARALLEL pass and an ASYNC statement each get a copy of the script’s variables: what they set stays their own, so they hand results on through tables. Once a pass fails no new one starts, and the loop fails with its error. AWAIT ALL fails with the first ASYNC failure’s error, and a script’s end waits for what it started. Neither runs inside a transaction.
• AWAIT $h fails with that statement’s error, which AWAIT ALL then leaves out. AWAIT 'id' waits for a procedure or file START CALL began, from any session, and fails with its error.
• A script run again with the same job (?job=…, a task’s tick) writes each statement once, loops, passes and ASYNC statements included.

CREATE TABLE staged (id BIGINT, amount DOUBLE);
INSERT INTO staged VALUES (1, 10), (2, 20), (3, NULL);
DECLARE $missing = (SELECT count(*) FROM staged WHERE amount IS NULL);
IF $missing > 0 THEN
  PRINT 'filling ' || $missing || ' missing amounts';
  UPDATE staged SET amount = 0 WHERE amount IS NULL;
END IF;
DECLARE $total = 0;
FOR r IN (SELECT id, amount FROM staged ORDER BY id) DO
  $total = $total + $r.amount;
END FOR;
BEGIN
  ASSERT $total > 100, 'only ' || $total;
EXCEPTION WHEN assert_failure THEN
  PRINT 'checked: ' || $error;
END;
SELECT $total AS total;

total
30.0

A backfill a few passes at a time, and two statements beside each other:

CREATE TABLE loads (part BIGINT, rows BIGINT);
FOR p IN (SELECT value AS n FROM generate_series(1, 6)) PARALLEL 3 DO
  INSERT INTO loads VALUES ($p.n, (SELECT count(*) FROM staged));
END FOR;
ASYNC INSERT INTO loads VALUES (7, 0);
$last = ASYNC INSERT INTO loads VALUES (8, 0);
AWAIT $last;
AWAIT ALL;
SELECT count(*) AS parts, sum(rows) AS rows FROM loads;

parts | rows
8     | 18

Schemas and other lakes

Statement	Syntax
Create a schema	CREATE SCHEMA [IF NOT EXISTS] s
Drop a schema	DROP SCHEMA [IF EXISTS] s [CASCADE]
Make a new lake	CREATE DATABASE [IF NOT EXISTS] name [LOCATION 'dir']: a lake beside this one, attached. On a folder of lakes it makes a new served database, and DROP DATABASE name drops one, folder and all
Branch a database	CREATE DATABASE [IF NOT EXISTS] name [LOCATION 'dir'] CLONE base [WITH (schemas = (s, …))] [WITH NO DATA]: a whole database as base is now, no data file copied (branches)
Bring a branch’s tables up to date	ALTER DATABASE name REFRESH [t, …]: those tables (a materialized view: the tables it reads; none named: every table the branch took from its base), and the views that follow them, as the base has them now, in one commit
Drop a branch	DROP DATABASE name: what it wrote goes; refused while it has branches of its own
Compare two tables	SELECT * FROM pondra.diff('a', 'b'): the rows of b that differ from a, with _change_type
Attach a lake	ATTACH 'dir' AS name; ATTACH 'url' AS name (TYPE delta | iceberg | kafka, …) for other engines
Detach	DETACH [IF EXISTS] name

A table is t (in schema public), s.t, or lake.s.t. Names are letters, digits, _ and -.

CREATE SCHEMA sales;
CREATE TABLE sales.regions (code VARCHAR, name VARCHAR);
INSERT INTO sales.regions VALUES ('eu', 'Europe');
CREATE DATABASE archive;
CREATE TABLE archive.public.old_orders (id BIGINT, item VARCHAR);
INSERT INTO archive.old_orders VALUES (0, 'scone');
SELECT item FROM archive.old_orders UNION ALL SELECT item FROM orders;
DETACH archive;
ATTACH 'archive' AS old;
SELECT * FROM old.old_orders;
DETACH old;
DROP SCHEMA sales CASCADE;

Views

Statement	Syntax
Stored view	CREATE [OR REPLACE] VIEW [IF NOT EXISTS] v AS SELECT …: a query run over the tables as they are when read
Materialized view	CREATE [OR REPLACE] MATERIALIZED VIEW [IF NOT EXISTS] v [(CONSTRAINT c CHECK | EXPECT (condition) [ON VIOLATION DROP ROW | FAIL], …)] [WITH (option = value, …)] AS SELECT … FROM t … [EMIT FINAL]: kept up to date as rows arrive; it may follow another materialized view (Flows). EMIT FINAL keeps each group of a time bucket (or SESSION(ts, INTERVAL …)) once it is over (windows)
Temporary	CREATE [OR REPLACE] TEMP TABLE [IF NOT EXISTS] t AS SELECT …, CREATE [OR REPLACE] TEMP VIEW [IF NOT EXISTS] v AS SELECT …: a session’s own
Rename	ALTER VIEW v RENAME TO w: a stored view, not a materialized one
Refresh	REFRESH MATERIALIZED VIEW v: taken, and nothing to do, since the view is current as rows arrive (dbt sends it)
Detach	ALTER MATERIALIZED VIEW v DETACH: its rows stop following and stay, as a table of that name (a GROUP BY view’s a merge table, its rows combining as they are read). What follows it keeps following its table. A view that keeps windows is refused
Drop	DROP VIEW [IF EXISTS] v, DROP MATERIALIZED VIEW [IF EXISTS] v

A materialized view is filled from the rows already in its table, then follows every new row. EMIT FINAL and its options make windows and sessions, and other options stream joins: see table options. WITH (history = 'id', sequence_by = 'at' [, delete_when = '…']) keeps every version of each key with its __start_at and __end_at (history per key). A temporary table lives in the memory of the node a session talks to, and ends with the session (a Postgres connection, or a Python or JavaScript client until close()).

CREATE VIEW tea_orders AS SELECT * FROM orders WHERE item = 'tea';
CREATE MATERIALIZED VIEW spend_by_item AS SELECT item, sum(amount) AS total, count(*) AS n FROM orders GROUP BY item;
INSERT INTO orders VALUES (4, 'cake', 3, 13.5);
SELECT * FROM spend_by_item ORDER BY item;

db.sql("CREATE TEMP TABLE picked AS SELECT id FROM orders WHERE qty > 1")
print(db.sql("SELECT o.item FROM orders o JOIN picked USING (id)").rows())

ALTER VIEW tea_orders RENAME TO tea_only;
DROP VIEW tea_only;
DROP MATERIALIZED VIEW spend_by_item;

Functions, procedures and tasks

Statement	Syntax
SQL function	CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] f(x [type] [= v], …) [RETURNS type] AS expression, or RETURNS type RETURN expression
Table function	CREATE FUNCTION f(…) RETURNS TABLE (c type, …) AS $$ SELECT … $$
Python function	CREATE FUNCTION f(x type) RETURNS type LANGUAGE python [WITH (…)] AS $$ … $$
Procedure	CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] p($x type [= v], …) AS BEGIN … END (or any one statement after AS)
Python procedure	CREATE PROCEDURE p($x type, …) LANGUAGE python AS $$ … $$
Call	CALL p(args) or CALL p(name => value)
Run a file	CALL run('etl/orders.sql', day => DATE '2026-09-29'): a .sql, .py or notebook file of the lake’s, its parameters by name (Run files as jobs)
Run code once	DO LANGUAGE python | sql $$ … $$ (the language may come after the code, as in Postgres)
Start	START CALL p(args): runs it without waiting, and answers with the run’s id (AWAIT it, or find it in pondra.runs)
Task	CREATE [OR REPLACE] TASK [IF NOT EXISTS] t SCHEDULE '5 minutes' | 'cron 0 2 * * * UTC' AS CALL p(…)
Task in a graph	CREATE TASK t AFTER a, b [WHEN condition] [WITH (retries = 2, retry_delay = '1 minute', timeout = '1 hour', on_failure = p)] AS statement (Run tasks in order)
Run a task now	EXECUTE TASK t [(name => value, …)]: its graph, with values for its $names
A task’s result	pondra.result('t'): what task t answered in the same run of its graph
Pause a task	ALTER TASK t SUSPEND, ALTER TASK t RESUME
Drop	DROP FUNCTION [IF EXISTS] f, DROP PROCEDURE …, DROP MACRO …, DROP TASK [IF EXISTS] t
List	SHOW USER FUNCTIONS, SHOW PROCEDURES, SHOW TASKS (each takes LIKE 'pattern')

A parameter is $name in a procedure’s body, and name or $name in a function’s; a function’s $name that isn’t one of its parameters is refused when it is made. DO runs a block of code once, with the caller’s rights: it needs an admin token, and Python needs a node started with --python. What the block prints comes back as notices, and its last expression, if it is a frame, table, list of dicts or value, comes back as rows (see Functions and procedures). Python functions and procedures need a node started with --python. Functions, procedures and tasks are kept in the lake, so every node and client has them. Every call of a procedure, and every file run, is a row of pondra.runs (see system columns).

CREATE FUNCTION net(x DOUBLE, rate DOUBLE = 0.2) RETURNS DOUBLE RETURN x * (1 - rate);
CREATE FUNCTION slug(t VARCHAR) RETURNS VARCHAR LANGUAGE python AS $$ return t.lower().replace(' ', '-') $$;
CREATE FUNCTION double_it(x) AS x * 2;
CREATE PROCEDURE log_order($item VARCHAR, $qty BIGINT = 1) AS BEGIN
  INSERT INTO orders VALUES (100, $item, $qty, 1.0);
  SELECT count(*) AS orders FROM orders;
END;
CALL log_order('scone');
CALL log_order(item => 'bun', qty => 3);
DO LANGUAGE sql $$ INSERT INTO orders VALUES (101, 'roll', 1, 2.0); SELECT count(*) AS orders FROM orders $$;
CREATE TASK hourly_scone SCHEDULE '60 minutes' AS CALL log_order('scone');
SELECT net(10.0) AS net, slug('Hello World') AS slug, double_it(4) AS four;
SHOW USER FUNCTIONS;
SHOW PROCEDURES;
SHOW TASKS;
DROP TASK hourly_scone;
DROP PROCEDURE log_order;
DROP FUNCTION net;
DROP FUNCTION slug;
DROP FUNCTION double_it;

Other spellings

Each thing above has one way to write it. The ways other databases write it work too, so SQL brought from them runs as it is:

Written as	Is	From
CREATE MACRO m(x, r := 0.2) AS …, DROP MACRO m	CREATE FUNCTION m(x, r = 0.2) AS …, DROP FUNCTION m	DuckDB
CREATE PROCEDURE p(x type DEFAULT v) LANGUAGE sql AS $$ … $$	CREATE PROCEDURE p($x type = v) AS BEGIN … END	Postgres
f(x type DEFAULT v), $1, RETURNS … RETURN …, AS 'expr', AS (expr)	a function’s parameters and body	Postgres, Snowflake, BigQuery
SET VARIABLE x = v, RESET VARIABLE x, getvariable('x')	$x = v, RESET $x, $x	DuckDB
$x := v, DECLARE $x type := v	$x = v, DECLARE $x type = v	Snowflake, Postgres
END IF, END WHILE, END FOR, END LOOP, END CASE	END	Postgres, Snowflake
FOR r IN (…)	FOR $r IN (…)	Postgres, Snowflake
AWAIT ALL	AWAIT	Snowflake
RAISE NOTICE 'x %', v	PRINT 'x ' || v	Postgres
EXECUTE IMMEDIATE FROM 'etl/x.sql' USING (day => v)	CALL run('etl/x.sql', day => v)	Snowflake
SELECT pondra.start('p', args)	START CALL p(args), as a function where a query is wanted	Pondra

Files and secrets

Statement	Syntax
Write files	COPY (SELECT …) TO 'url' (FORMAT parquet | csv | json | delta | iceberg, PARTITION_BY (col, …), APPEND | OVERWRITE, HEADER true, DELIMITER ';', COMPRESSION zstd, ROW_GROUP_SIZE n)
Write a topic	COPY (SELECT …) TO 'kafka://brokers/topic' (FORMAT json, KEY col)
Make a secret	CREATE [OR REPLACE] SECRET [IF NOT EXISTS] name (TYPE s3 | r2 | gcs | azure | http | iceberg | kafka | generic, KEY value, …, SCOPE 'url')
A session’s own	CREATE [OR REPLACE] TEMPORARY SECRET [IF NOT EXISTS] name (…): in the node’s memory for this session only, used before the lake’s
Drop a secret	DROP [TEMPORARY] SECRET [IF EXISTS] name

COPY table TO … is short for COPY (SELECT * FROM table) TO …. A URL needs a secret whose SCOPE covers it (a user’s query, one it has USAGE on), and only an admin may write files. A path on a machine is written only by the program that started the node: the shell, local(), or pondra sql. Each secret is sealed with a key of its own, which the master key (PONDRA_SECRET_KEY, or a key service: PONDRA_KMS_COMMAND) wraps; SELECT * FROM secrets() lists their names and scopes, never their values.

Users and grants

Statement	Syntax
Make a user or a role	CREATE USER [IF NOT EXISTS] name [WITH] [PASSWORD 'at least 8'] [SUPERUSER] [MAX_QUERIES n] [STATEMENT_TIMEOUT '5 minutes'], CREATE ROLE name
Change one	ALTER USER name [WITH] PASSWORD '…' | SUPERUSER | NOSUPERUSER | MAX_QUERIES n | STATEMENT_TIMEOUT '…' (0: no limit)
Drop one	DROP USER [IF EXISTS] name, DROP ROLE [IF EXISTS] name
Grant	GRANT SELECT [(col, …)] | INSERT | UPDATE | DELETE | ALL [PRIVILEGES] ON [TABLE] t | SCHEMA s | ALL TABLES [IN SCHEMA s] TO name, …
Grant a secret	GRANT USAGE ON SECRET s TO name, …
Grant a role	GRANT role, … TO user, …
Take back	REVOKE … FROM …, as GRANT names them (REVOKE SELECT (col) ON t FROM r: that column only)
A token	CREATE TOKEN name FOR [USER] u [EXPIRES IN '90 days'] (answers it, once), DROP TOKEN name FOR [USER] u
List	SELECT * FROM pondra.users, SELECT * FROM pondra.grants
What was done, and refused	SELECT * FROM pondra.audit (a superuser’s): at, user, door, from, class, statement, outcome, message, ms, node
Every statement	SELECT * FROM pondra.history (a superuser sees every row, anyone else their own): at, id, user, door, from, node, session, class, statement, outcome, error, ms, rows, nodes; fingerprint (the statement with its literals left out: one query’s runs share it), plan_id (the shape of the plan it ran), version (the commit it read at: t AT (VERSION => n) reads what it read), reads and writes (the tables and views it read, the tables it changed), misestimate (for a query of PONDRA_LEARN_MS or more, a factor: how many times more or fewer rows its worst join gave than expected, so 40 is forty times off); learned (as JSON, each filter on a table that kept 2× or more of its rows more or fewer than expected: its table, conditions, and the shares expected and kept); and for a statement slower than PONDRA_SLOW_MS, or ten times off, its plan (each step’s rows beside its expected rows, and its time) and trace (each node’s share, as JSON)
What the lake learned	SELECT * FROM pondra.learned (from the history you may read): object, kind (filter), about (its conditions), expected and actual (the shares of the rows read it was expected to keep and kept), runs, updated_at; each fact as last seen

public is every user: GRANT SELECT ON t TO public. A grant on a schema covers its tables made later too. Only a superuser (or the admin token) runs these. MAX_QUERIES and STATEMENT_TIMEOUT are a user’s quota on each node. See security.

pondra sql --dir scratch "CREATE TABLE t (a BIGINT, region VARCHAR)"
pondra sql --dir scratch "INSERT INTO t VALUES (1, 'eu'), (2, 'us')"
pondra sql --dir scratch "COPY (SELECT * FROM t) TO 'out.parquet'"
pondra sql --dir scratch "COPY (SELECT * FROM t) TO 'by_region/' (FORMAT csv, PARTITION_BY (region), HEADER true)"
pondra sql --dir scratch "SELECT count(*) AS n FROM 'out.parquet'"
pondra sql --dir scratch "CREATE TABLE t_copy AS SELECT * FROM 'out.parquet'"
pondra sql --dir scratch "INSERT INTO t_copy SELECT * FROM read_parquet('out.parquet')"

pondra sql reads those files in CREATE TABLE … AS and INSERT … SELECT too. On a folder with no lake yet, a query says so and a write makes one.

COPY … TO takes DataFusion’s words too: STORED AS, PARTITIONED BY and OPTIONS. 'format.has_header' means HEADER, 'format.delimiter' means DELIMITER and 'format.max_row_group_size' means ROW_GROUP_SIZE. The DuckDB form, (FORMAT csv, HEADER false), is unchanged.

pondra sql --dir scratch "COPY t TO 'words/' STORED AS CSV PARTITIONED BY (region) OPTIONS ('format.has_header' 'false')"

CREATE SECRET sales (TYPE s3, KEY_ID 'your-key-id', SECRET 'your-secret', REGION 'eu-west-1', SCOPE 's3://my-bucket');
SELECT * FROM secrets();
DROP SECRET sales;

Shares and recipients

A share hands tables to another company through the Delta Sharing protocol: they read the files with their own compute. See Share a table.

Statement	Syntax
Make a share	CREATE SHARE [IF NOT EXISTS] name [COMMENT '…']
Add a table	ALTER SHARE s ADD [TABLE] t [PARTITION (col = 'v'), …] [AS schema.name] [WITH HISTORY | WITHOUT HISTORY]; also GRANT SELECT ON TABLE t TO SHARE s
Take one out	ALTER SHARE s REMOVE [TABLE] name; also REVOKE SELECT ON TABLE t FROM SHARE s
Make a recipient	CREATE RECIPIENT [IF NOT EXISTS] name [COMMENT '…'] [EXPIRES IN '90 days'] (answers its profile, once)
A new token	ALTER RECIPIENT name ROTATE TOKEN [EXPIRES IN '…'] (answers the new profile; the old token ends)
Grant, take back	GRANT SELECT ON SHARE s TO RECIPIENT r, …, REVOKE SELECT ON SHARE s FROM RECIPIENT r, …
Drop	DROP SHARE [IF EXISTS] name, DROP RECIPIENT [IF EXISTS] name
List	SHOW SHARES, SHOW RECIPIENTS, DESCRIBE SHARE s (SHOW ALL IN SHARE s); pondra.shares, pondra.recipients
Read a share	ATTACH '<profile JSON or endpoint>' AS name (TYPE share [, SHARE 'name'] [, TOKEN '…']), then name.schema.table

CREATE OR REPLACE SHARE and CREATE OR REPLACE RECIPIENT are refused, as they are for schemas and users: replacing a share would drop its tables and grants, and replacing a recipient would end its token.

External tables

CREATE EXTERNAL TABLE is DataFusion’s statement for naming files. In Pondra it makes a stored view of them: SELECT * FROM read_csv('…', …) (or read_parquet, read_json) under the name you give. Nothing is copied, and information_schema lists the table as a VIEW.

Statement	Syntax
Create	CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] t [(column type, …)] STORED AS PARQUET | CSV | JSON LOCATION 'path or URL' [PARTITIONED BY (column, …)] [OPTIONS ('name' 'value', …)]; UNBOUNDED is accepted after CREATE
Add a file	INSERT INTO t [(column, …)] VALUES … | SELECT …, when LOCATION is a folder
Drop	DROP TABLE t, or DROP VIEW t: the name goes and the files stay

• Columns. Without a column list, the table has the files’ own schema. With one, each column is read as the type you declare: a CSV’s by position, Parquet’s and JSON’s by name. A declared column that Parquet files don’t have is NULL.
• LOCATION. A relative path becomes absolute when the table is made, as ATTACH does. A folder (a path ending in /, or one that exists) reads its files of that format. With declared columns, a folder with no files yet is an empty table. Without columns, no files is an error: no files at ….
• PARTITIONED BY (day), with day among the declared columns: the folders day=…/ give it, typed as declared.
• OPTIONS. CSV reads 'format.has_header', 'format.delimiter', 'format.quote', 'format.escape', 'format.comment' and 'format.terminator'. Options about how files are written, such as Parquet’s 'format.compression' and 'format.max_row_group_size', are accepted and ignored. Any other option is refused by name.
• Writing. INSERT INTO a table whose LOCATION is a folder writes a new file into that folder, in the table’s format. A partitioned table writes into day=…/ folders. INSERT INTO t (col) VALUES … fills the other columns with NULL.
• Who reads the files. A path on the node’s machine is read and written only by the program that started the node: the shell or pondra.local(). A URL needs a secret that covers it, as for read_parquet.

printf 'id,name\n1,tea\n2,cake\n' > menu.csv
pondra scratch <<'SQL'
CREATE EXTERNAL TABLE menu STORED AS CSV LOCATION 'menu.csv';
SELECT * FROM menu ORDER BY id;
CREATE EXTERNAL TABLE events (id INT, name VARCHAR) STORED AS CSV LOCATION 'events/' OPTIONS ('format.has_header' 'false');
INSERT INTO events VALUES (1, 'one'), (2, 'two');
SELECT * FROM events ORDER BY id;
DROP TABLE menu
SQL

{"table":"menu","files":true}
+----+------+
| id | name |
+----+------+
| 1  | tea  |
| 2  | cake |
+----+------+
{"table":"events","files":true}
{"rows":2}
+----+------+
| id | name |
+----+------+
| 1  | one  |
| 2  | two  |
+----+------+
{"table":"menu","dropped":true}

events/ is now a folder with a file in it, and menu.csv is still there. A URL or a path in a query needs no table: see names.

Every kind of object

Every kind of object a lake holds (schemas, tables, views, materialized views, external tables, sequences, functions, macros, procedures, tasks, secrets, users, roles and the lakes attached) takes the same words for being listed, described and made again.

Statement	Meaning
SELECT … FROM pondra.objects	every object, of every kind: kind, lake, schema, name, comment, and definition, the statements that make it again (system tables)
SHOW CREATE TABLE t (or VIEW, MATERIALIZED VIEW, SEQUENCE, FUNCTION, PROCEDURE, TASK, SCHEMA, ROLE, DATABASE)	the statements that make it again, its comments included, as one definition; a branch’s is the CREATE DATABASE … CLONE that made it, of its base as the base is then
COMMENT ON TABLE t IS 'text'	describe it; any kind above, COLUMN t.c too, and IS NULL takes it away. A comment follows a rename and goes with a drop
COMMENT IF EXISTS ON TABLE t IS 'text'	the same, and nothing if t isn’t there
CREATE OR ALTER TABLE t (…) [layout clauses] [WITH (…)]	make t, or bring the one there to this definition without losing a row: columns added at the end, types widened, the layout and options set as written (one left out goes back to its default). A column taken away, moved or narrowed, and a changed key, partition, NOT NULL, DEFAULT or CHECK, is refused, saying which
CREATE OR ALTER VIEW v AS SELECT …	the same as CREATE OR REPLACE VIEW
SELECT … FROM pondra.kinds	the kinds there are, and the statements each takes

CREATE OR ALTER TABLE is for a project’s files that run again and again (dbt, a deployment): the file says what the table is, and running it changes only what differs. Its answer says what it did: created, unchanged, or altered with each change.

CREATE TABLE stock (sku VARCHAR PRIMARY KEY, qty BIGINT) CLUSTER BY (sku);
COMMENT ON TABLE stock IS 'what the shop holds';
COMMENT ON COLUMN stock.qty IS 'units on the shelf';
CREATE OR ALTER TABLE stock (sku VARCHAR PRIMARY KEY, qty BIGINT, shelf VARCHAR) CLUSTER BY (sku);
SHOW CREATE TABLE stock;

CREATE TABLE stock (
  sku VARCHAR,
  qty BIGINT,
  shelf VARCHAR,
  PRIMARY KEY (sku)
)
CLUSTER BY (sku);
COMMENT ON TABLE stock IS 'what the shop holds';
COMMENT ON COLUMN stock.qty IS 'units on the shelf';

SELECT kind, name, comment FROM pondra.objects WHERE schema = 'public' ORDER BY name;

Maintenance

Statement	Meaning
CHECKPOINT	tier every table’s log into Parquet now, and write the catalog down, so other engines read what is current
ANALYZE [t]	taken, and nothing to do: a table’s statistics are kept with every write (BI tools send it)

CHECKPOINT;

Refused

Pondra refuses these by name, so a statement never runs and quietly does something else:

Statement	What happens
ALTER TABLE t RENAME TO u when u is taken, or when a materialized view or a task follows t	refused, with the name
ALTER VIEW v RENAME TO w of a materialized view	refused; DROP it, and CREATE it under the new name
ALTER TABLE t ADD COLUMN c … NOT NULL or … DEFAULT	refused; no file is rewritten, so add the column, then UPDATE it
a NOT NULL column given no value, or a DEFAULT that doesn’t cast	refused, naming the column
a value that doesn’t cast to its column’s type ('abc' into a BIGINT)	refused, naming the value; never stored as NULL
INSERT … ON DUPLICATE KEY UPDATE	refused: it is MySQL’s; write ON CONFLICT (…) DO UPDATE SET … or MERGE
INSERT OVERWRITE	refused; write BEGIN; DELETE FROM t; INSERT INTO t …; COMMIT, one commit
INSERT, UPDATE or DELETE with RETURNING	refused, not taken yet; read the rows with a SELECT after it
CREATE TABLE … ENGINE = …, … TBLPROPERTIES (…)	refused; a table’s options go in WITH (…)
CREATE TABLE … WITH (…) with an option name Pondra doesn’t know	refused, with the name; see table options
CREATE TEMPORARY EXTERNAL TABLE	refused; use CREATE TEMP VIEW v AS SELECT * FROM read_parquet('…')
CREATE EXTERNAL TABLE … STORED AS AVRO	refused; Pondra reads Parquet, CSV, JSON (one value a line) and Arrow
CREATE TABLE t when t is there	refused (42P07); say IF NOT EXISTS or OR REPLACE
SET datafusion.runtime.…	refused: the node’s memory and caches are set as it starts
MERGE with no WHEN clause, a column set or inserted twice, a SET of the source’s column, or a source named as the target	refused, saying which
CREATE EXTERNAL TABLE over compressed CSV or JSON ('format.compression' 'gzip')	refused; not read yet
CREATE EXTERNAL TABLE … OPTIONS (…) with an option Pondra doesn’t read	refused, with the name
INSERT into an external table whose LOCATION is one file	refused: a view of a folder takes INSERTs
UPDATE, DELETE and ALTER on an external table	refused
a merge table with a non-key column that has no merge function	refused at CREATE (s needs a merge function)
ALTER TABLE t SET (partition_by = …)	refused; each file holds one partition
UPDATE or DELETE on a merge table	refused; INSERT into it instead, and the key’s rows combine
UPDATE that sets a key column	refused; the key names the row
a column called _row_id, _version, _created_at or _updated_at	refused: every table has them already
DROP TABLE while a view or task reads the table	refused; drop the view or task first
a change to a table a window, session, join or min/max view follows	refused, with the view’s name, because such a view cannot take a row back
a FOREIGN KEY or REFERENCES without NOT ENFORCED; UNIQUE NULLS NOT DISTINCT; DEFERRABLE; ALTER TABLE … ADD PRIMARY KEY	refused, saying why: a reference isn’t checked yet; a table’s key is made with it
POST /append, COPY … FROM STDIN, Kafka, Flight or another engine’s commit into a table with a UNIQUE	refused, by name: only SQL’s writes are checked on the leader
CREATE UNIQUE INDEX on an expression, or with WHERE or INCLUDE; CREATE INDEX … USING hnsw | ivfflat | bm25	refused by name (Indexes: a unique index on columns is a UNIQUE constraint; any other index is kept, nothing is built)
SAVEPOINT, ROLLBACK TO	refused: a transaction commits or rolls back whole
CREATE, ALTER, DROP inside BEGIN … COMMIT	refused (0A000): run them outside the transaction
COPY … TO a path on the node’s machine	refused unless the program that started the node sends it
COPY … TO a folder inside a lake	refused; a lake’s files are its own
Python functions and procedures on a node started without --python	refused, with the flag to use