> ## Documentation Index
> Fetch the complete documentation index at: https://oxy.tech/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL support

> What SQL you can run on Airhouse and how Postgres compatibility works.

Airhouse passes your SQL straight to [DuckDB](https://duckdb.org/docs/stable/sql/introduction) with the [DuckLake](https://ducklake.select/docs/stable/) extension loaded. There's no separate Airhouse SQL dialect: if DuckDB and DuckLake support a statement, you can run it through any Postgres client.

<Note>
  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.
</Note>

## 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](./users-and-roles).

## 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:

```sql theme={null}
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:

```sql theme={null}
-- 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](../connect/drivers).
* **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](../reference/configuration).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.