Postgres port

A node speaks the Postgres wire protocol on the address you give --pg. psql, drivers (psycopg, asyncpg, JDBC, Go, Node) and BI tools connect to it as they would to Postgres. Queries and writes run exactly as POST /sql runs them, so anything SQL does works here.

The port also answers Postgres’s catalog (pg_catalog and information_schema), so psql’s \dt, \d, \dv, \dn, \df, \l and \du work, and so do SQLAlchemy’s inspector, pgjdbc (DBeaver, DataGrip, Metabase), psqlODBC (Tableau, Excel) and dbt-postgres. dbt and BI tools has the settings.

pondra serve lake --pg 127.0.0.1:5432

Connect

Setting	Value
host, port	the address of `--pg`
database	any name on a node that serves one lake. `SELECT current_database()` gives the lake’s name. On a folder of lakes, the database in the connection picks the lake
user, password	with no tokens set and no user who signs in, anything. Otherwise a user and its password, signed in with SCRAM-SHA-256 as Postgres does it (a user with only tokens gives one as its password); or `reader`, `writer` or `admin` and that role’s token
SSL	not offered yet: use `sslmode=disable`, or `prefer`

SELECT version() answers PostgreSQL 16.0 (Pondra on Apache DataFusion), and SHOW server_version answers 16.0.

The roles are the same as for HTTP: reader may query, writer may also insert, update, delete and copy in, and admin may also create tables and other objects.

psql "postgresql://reader:$READ_TOKEN@db.example.com:5432/lake"

psql
Python

psql "postgresql://admin@localhost:5432/lake" <<'EOF'
CREATE TABLE orders (id BIGINT, item VARCHAR, amount DOUBLE, ts TIMESTAMP);
INSERT INTO orders VALUES (1, 'tea', 7.5, TIMESTAMP '2026-09-01 10:00:00'), (2, 'cake', 4.5, TIMESTAMP '2026-09-01 11:30:00');
SELECT item, sum(amount) AS total FROM orders GROUP BY item ORDER BY item;
EOF

import psycopg

with psycopg.connect("host=localhost port=5432 user=admin dbname=lake", autocommit=True) as conn:
    print(conn.execute("SELECT item, sum(amount) AS total FROM orders GROUP BY item ORDER BY item").fetchall())

CREATE TABLE
INSERT 0 2
 item | total
------+-------
 cake |   4.5
 tea  |   7.5
(2 rows)

A driver’s transactions are Pondra’s: BEGIN … COMMIT is one commit, with snapshot isolation (Postgres’s REPEATABLE READ), and the connection’s status says when one is open, as drivers expect (Transactions). autocommit=True makes every statement its own commit.

What runs

You send	Result
a query	rows, as text or binary, whichever the client asks for
`CREATE`, `ALTER`, `DROP`, `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `COPY … TO 'url'`	the same as over HTTP, answered with a command tag such as `INSERT 0 2`
`CALL p(…)`	the procedure’s rows, or `CALL`. What it prints comes as `NOTICE` messages, which psql shows
`DO LANGUAGE python \| sql $$ … $$`	the block’s rows, or the tag `DO`. What Python prints comes as `NOTICE` messages. It needs an admin, and Python needs a node started with `--python`
`BEGIN`, `START TRANSACTION`, `COMMIT`, `END`, `ROLLBACK`, `ABORT`	a transaction: its statements one commit
`SAVEPOINT`, `RELEASE`, `ROLLBACK TO`	refused (`0A000`): a transaction commits or rolls back whole
`SET`, `RESET`, `SHOW`	the connection’s settings: DataFusion’s `datafusion.*` options checked and used by its queries, Postgres’s names kept and shown
`PREPARE`, `EXECUTE`, `DEALLOCATE`	the connection’s prepared statements (a `DEALLOCATE` of a name the protocol prepared: accepted)
`DISCARD`, `CLOSE`	accepted, and ignored
`SHOW server_version`, `standard_conforming_strings`, `transaction_isolation`, `client_encoding`	a fixed answer, unless the connection set it
`SHOW TABLES`	the lake’s tables and views, and the `information_schema` views
`DESCRIBE t`	its columns and types, with Arrow’s type names

A connection is a session: CREATE TEMP TABLE, temporary views, settings and prepared statements are the connection’s own, and end when it closes. So do its Python variables: DO LANGUAGE python blocks on one connection share them (Functions and procedures). A request with several statements, separated by ;, runs them in order.

psql "postgresql://admin@localhost:5432/lake" -c "SHOW TABLES" -c "DESCRIBE orders"

 table_catalog |    table_schema    | table_name  | table_type
---------------+--------------------+-------------+------------
 lake          | public             | orders      | BASE TABLE
 lake          | information_schema | tables      | VIEW
 lake          | information_schema | views       | VIEW
 lake          | information_schema | columns     | VIEW
 lake          | information_schema | df_settings | VIEW
 lake          | information_schema | schemata    | VIEW
 lake          | information_schema | routines    | VIEW
 lake          | information_schema | parameters  | VIEW
(8 rows)

 column_name | data_type     | is_nullable
-------------+---------------+-------------
 id          | Int64         | YES
 item        | Utf8View      | YES
 amount      | Float64       | YES
 ts          | Timestamp(µs) | YES
(4 rows)

DO blocks

DO runs a block of code once, as Postgres’s does. The language is python or sql, before the code or after it. What Python prints comes back as NOTICE messages, and the command tag is DO. A block needs an admin, and Python needs a node started with --python. The connection inside it is db.

psql "postgresql://admin@localhost:5432/lake" <<'EOF'
DO LANGUAGE python $$
n = db.sql("SELECT count(*) FROM orders").item()
print("orders:", n)
$$;
EOF

NOTICE:  orders: 4
DO

See Functions and procedures for what a block can return, and how it fails.

Parameters

A driver’s $1, $2 (or %s) parameters are bound into the statement as literals: numbers as they are, anything else quoted. A parameter’s type is what the client says, or what the query implies (WHERE id = $1 takes id’s type). Arrays of real, double precision, integer and bigint are bound as arrays, for vector search.

with psycopg.connect("host=localhost port=5432 user=admin dbname=lake", autocommit=True) as conn:
    print(conn.execute("SELECT id, item FROM orders WHERE amount > %s AND item = %s", (5, "tea")).fetchall())

[(1, 'tea')]

Types

A column comes to the client as the Postgres type closest to its type in the lake.

In the lake	Postgres type
`BOOLEAN`	`bool`
`TINYINT`, `SMALLINT`	`int2`
`INT`	`int4`
`BIGINT`	`int8`
`REAL`	`float4`
`DOUBLE`	`float8`
`DECIMAL(p, s)`	`numeric`
`DATE`	`date`
`TIME`	`time`
`TIMESTAMP`	`timestamp`, in microseconds, without a time zone
`TIMESTAMPTZ`	`timestamptz`: an instant, sent in UTC
`BYTEA`, `BINARY`, `VARBINARY`, `BLOB`	`bytea`
a list (`VARCHAR[]`, `INT[]`, `FLOAT[]`)	a Postgres array, in text and binary format: a `VARCHAR[]` column shows as `character varying[]`
`VARCHAR`, and every other type (structs, `JSON`, `VARIANT`)	`varchar`, as text

A struct reaches the client as its text. Cast in the query if a driver needs another type.

COPY

COPY … TO STDOUT sends a table or a query, in text, CSV or binary. COPY table FROM STDIN loads text or CSV into a table, one batch at a time, through the log. psql’s \copy and psycopg’s cursor.copy both work.

	`COPY … TO STDOUT`	`COPY … FROM STDIN`
source or target	a table, columns of it, or `(query)`	a table, with a column list if you like
`FORMAT`	`text` (default), `csv`, `binary`	`text` (default), `csv`
options	`HEADER`, `DELIMITER`, `NULL`	`HEADER`, `DELIMITER`, `NULL`

The default NULL is \N in text and empty in CSV. COPY into a lake you attached is refused: INSERT INTO it instead. A column you leave out is null. COPY … TO 'url' (with a quoted URL, not STDOUT) is not this: it is the files statement.

printf 'id,item,amount\n3,juice,2.5\n4,scone,3\n' > more.csv
psql "postgresql://admin@localhost:5432/lake" -c "\copy orders (id, item, amount) FROM 'more.csv' WITH (FORMAT csv, HEADER)"
psql "postgresql://admin@localhost:5432/lake" -c "\copy (SELECT id, item FROM orders ORDER BY id) TO 'orders.csv' CSV HEADER"
cat orders.csv

COPY 2
COPY 4
id,item
1,tea
2,cake
3,juice
4,scone

The count after COPY … TO is the number of rows: it says 4 for 4 rows, and the header line isn’t counted.

In Python, with psycopg:

with psycopg.connect("host=localhost port=5432 user=admin dbname=lake", autocommit=True) as conn:
    with conn.cursor().copy("COPY orders (id, item, amount) FROM STDIN") as copy:
        copy.write_row((5, "bun", 1.5))
    print(conn.execute("SELECT count(*) FROM orders").fetchone())

(5,)

The catalog

Drivers and tools look at Postgres’s catalog when they connect and when you browse. Pondra answers pg_catalog (pg_type, pg_class, pg_namespace, pg_attribute, pg_index, pg_constraint, pg_proc, pg_database, pg_roles and the other tables they read) and information_schema, with the lake’s tables, views, schemas, functions and keys. That is what psql’s backslash commands, SQLAlchemy, JDBC, ODBC and dbt read:

psql "postgresql://admin@localhost:5432/lake" -c '\dt' -c '\d orders'

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | orders | table | admin
(1 row)

                        Table "public.orders"
 Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
 id     | bigint                      |           |          |
 item   | character varying           |           |          |
 amount | double precision            |           |          |
 ts     | timestamp without time zone |           |          |

with psycopg.connect("host=localhost port=5432 user=admin dbname=lake", autocommit=True) as conn:
    print(conn.execute("SELECT table_name FROM information_schema.tables WHERE table_name = 'orders'").fetchall())

[('orders',)]

Drivers

These connect and query, run in this repository’s tests or checked by hand:

Driver	Notes
`psql`	queries, `\copy`, `CALL`, `DO`, and the backslash commands `\dt`, `\d`, `\dv`, `\dn`, `\df`, `\l` and `\du`
psycopg 3 and psycopg 2	`autocommit=True`; `cursor.copy` works
asyncpg	queries and parameters
ADBC (`adbc_driver_postgresql`)	Arrow results, through `COPY … TO STDOUT (FORMAT binary)`
SQLAlchemy (`postgresql+psycopg2` and `postgresql+psycopg`)	the inspector: schemas, tables, views, columns, primary keys; pandas’ `to_sql` and `read_sql`
pgjdbc, psqlODBC	catalog calls and queries: DBeaver, DataGrip, Metabase, Tableau and Excel connect through them
Npgsql 4.0 and 8 (.NET)	the types it loads on connecting, `GetSchema`, parameters, arrays and decimals: Power BI Desktop’s PostgreSQL connector uses it
dbt-postgres	seeds, models, snapshots and tests. See dbt and BI tools

import adbc_driver_postgresql.dbapi as adbc

with adbc.connect("postgresql://admin@localhost:5432/lake") as conn:
    cur = conn.cursor()
    cur.execute("SELECT id, item FROM orders ORDER BY id LIMIT 2")
    print(cur.fetch_arrow_table().to_pydict())

{'id': [1, 2], 'item': ['tea', 'cake']}

Error codes

Errors carry Postgres’s codes (SQLSTATE), so a driver can tell them apart. The same code is on every door: HTTP’s x-pondra-sqlstate header, Flight SQL’s metadata, PondraError.sqlstate in Python and err.sqlstate in JavaScript.

Code	When
`23514`	a row breaks a `CHECK`, or a view’s expectation that fails writes
`23502`	a `NOT NULL` column left empty
`40001`	a transaction’s row changed by another commit since it began: run it again
`25P02`	a statement in a transaction that already failed
`42P01`, `42703`, `42883`	no such table, column, function
`42601`	a syntax error
`42501`	a user’s grants don’t allow it
`42P07`	a table or view of that name exists
`22012`, `22003`, `22P02`	division by zero, a number out of range, a value that doesn’t cast
`57014`, `53200`	a statement timeout; out of memory
`0A000`	not supported
`XX000`	anything else