SQL support

Airhouse passes your SQL straight to DuckDB with the DuckLake extension loaded. There’s no separate Airhouse SQL dialect: if DuckDB and DuckLake support a statement, you can run it through any Postgres client.

The SQL dialect is DuckDB’s, which is close to PostgreSQL but not identical. When a function or syntax behaves unexpectedly, check the DuckDB documentation.

Supported statements

Category	Examples
Queries	`SELECT`, joins, CTEs (`WITH`), subqueries, window functions, aggregates, `GROUP BY ALL`, `EXPLAIN`
Data changes	`INSERT`, `UPDATE`, `DELETE`, `MERGE INTO`, `COPY ... FROM STDIN`
Schema changes	`CREATE TABLE`, `CREATE TABLE ... AS SELECT`, `ALTER TABLE`, `DROP TABLE`, `CREATE SCHEMA`, `CREATE VIEW`
Transactions	`BEGIN`, `COMMIT`, `ROLLBACK`
Introspection	`SHOW TABLES`, `DESCRIBE`, `information_schema`

Which statements you can run depends on your role. See Users, roles, and security.

Name your tables with the lake catalog

Your persistent tables live in the DuckLake catalog called lake. Every session starts with lake as its current catalog and main as its schema, so orders and lake.main.orders name the same table. Fully qualified names are still the safest choice, because they keep working if a tool or script changes the session’s current catalog or search path:

CREATE SCHEMA lake.analytics;
CREATE TABLE lake.analytics.daily_revenue AS
SELECT date_trunc('day', created_at) AS day, sum(amount) AS revenue
FROM lake.main.events
WHERE kind = 'purchase'
GROUP BY ALL;

Tables created in other catalogs, such as DuckDB’s memory or temp, only exist for the life of your connection.

Analytical SQL

Because DuckDB runs your queries, you get its full analytical feature set:

-- Running total per kind with a window function
SELECT
  kind,
  created_at,
  amount,
  sum(amount) OVER (PARTITION BY kind ORDER BY created_at) AS running_total
FROM lake.main.events
ORDER BY kind, created_at;

-- Filter aggregate results without a subquery
SELECT kind, count(*) AS n
FROM lake.main.events
GROUP BY kind
HAVING count(*) > 1;

Postgres compatibility

Airhouse implements the parts of the Postgres protocol that clients rely on:

Authentication: SCRAM-SHA-256 password authentication, with optional TLS.
Simple query protocol: fully supported. This is what psql uses.
Extended query protocol: prepared statements with parameter binding, used by JDBC, psycopg2, and most drivers. Common use works; complex prepared-statement flows can fail. See the tips in Connect from code.
Command tags: responses carry Postgres command tags such as SELECT 3, INSERT 0 2, and CREATE TABLE, so drivers report row counts correctly.
COPY FROM STDIN: bulk loading with COPY ... FROM STDIN, including psql’s \copy.
Catalog introspection: queries against pg_catalog.pg_class, pg_namespace, pg_attribute, pg_database, and pg_type are answered from DuckDB’s information_schema, so psql meta-commands such as \dt, \dn, and \l, and the schema browsers in GUI tools, work.

Things that differ from PostgreSQL:

Functions, types, and syntax follow DuckDB. PostgreSQL-specific features that DuckDB doesn’t have (for example LISTEN/NOTIFY, stored procedures in PL/pgSQL, or Postgres extensions) aren’t available.
The pg_catalog answers are a compatibility layer, not a full copy of Postgres’ system catalogs. Tools that depend on detailed catalog internals may show partial metadata.

DuckDB extensions

INSTALL and LOAD are limited to an allowlist of extensions that the server operator controls. By default the allowlist is ducklake, httpfs, json, h3, and spatial. Loading any other extension fails with SQLSTATE 42501. If you run Airhouse yourself, see allowed_extensions in the configuration reference.

Get Started

Concepts

Connect

Guides

Reference

SQL support

Supported statements

Name your tables with the lake catalog

Analytical SQL

Postgres compatibility

DuckDB extensions

​Supported statements

​Name your tables with the lake catalog

​Analytical SQL

​Postgres compatibility

​DuckDB extensions

Supported statements

Name your tables with the lake catalog

Analytical SQL

Postgres compatibility

DuckDB extensions