---
title: "Postgres wire protocol"
description: "Connect with standard Postgres clients and drivers."
canonical_url: "https://surrealdb.com/docs/reference/protocols/postgres-protocol"
section: "Protocols and APIs"
surrealdb_version: "4.x"
last_modified: "2026-11-19T06:06:22Z"
---

> Full SurrealDB documentation index: https://surrealdb.com/docs/llms.txt

# Postgres wire protocol

Connect with standard Postgres clients and drivers.

_(since v3.3.0)_

> [!IMPORTANT]
> From SurrealDB 4.0, a connection runs the [PostgreSQL dialect of SQL](#postgresql-sql), and can switch to ISO GQL. SurrealQL is not available over this protocol from 4.0: send it over [HTTP](/docs/reference/protocols/http-protocol.md), [RPC](/docs/reference/protocols/rpc-protocol.md) or an SDK. Before 4.0, a connection runs SurrealQL unless it selects ISO GQL, and ANSI SQL is not supported.

The Postgres wire protocol listener lets **any Postgres client** - `psql`, JDBC, `tokio-postgres`, Npgsql, and similar tools - connect to SurrealDB on a TCP port and run queries. The server speaks **Postgres protocol v3.0** (simple and extended query flows, prepared statements, interactive transactions, cancellation, optional TLS).

From SurrealDB 4.0, clients send PostgreSQL queries, and the SQL that drivers, ORMs and BI tools generate, against SurrealDB tables. A connection can also select ISO GQL. In SurrealDB 3.3 and later 3.x releases, clients send SurrealQL by default and can select ISO GQL.

This feature ships with builds that include the `postgres` server feature (enabled in the default feature set). It is opt-in at runtime: the listener starts only when you pass [`--postgres-bind`](/docs/reference/cli/db/commands/start.md) via the `surreal db start` command.

## Why Postgres protocol on SurrealDB?

Postgres is the de facto wire standard for tabular database access. A huge ecosystem already knows how to connect, authenticate, run queries, and consume typed rows through it.

That matters even when the database is not Postgres:

- **`psql` as a REPL**: quick ad hoc queries without HTTP or a SurrealDB-specific shell.
- **Existing drivers and pools**: reuse JDBC / async-postgres / sqlx-style infrastructure in apps and jobs.
- **BI and SQL-oriented tools**: many products speak Postgres first, so a wire-compatible port lets them connect and run the SQL they generate.

## Included features

The following chart shows features currently available, along with those that are not yet implemented.

| Included | Not yet included |
| --- | --- |
| Simple query protocol (`Q` messages) | MD5 auth (legacy Postgres; not planned) |
| Extended query protocol (Parse / Bind / Execute / Describe / Sync) | `LIVE` queries over Postgres |
| Cleartext password auth (existing IAM path) | GQL inside an open interactive transaction |
| Namespace/database via startup `database=ns/db` | Full static typing for every `prepare` shape |
| Interactive `BEGIN` / `COMMIT` / `ROLLBACK` | `UPDATE ... FROM` and `DELETE ... USING` |
| SCRAM-SHA-256 auth over SASL (when the user has SCRAM verifier material - see [Authentication](#authentication)) | |
| Positional parameters (`$1`, `$2`, …) | |
| Typed result columns inferred from values | |
| TLS via existing `--web-crt` / `--web-key` | |
| Query cancellation (`CancelRequest`) | |
| Dialect switch to ISO GQL (`SET dialect = 'gql'`) | |
| From 4.0: the PostgreSQL SQL dialect, as the default | |
| From 4.0: the `pg_catalog` and `information_schema` relations, which list the tables of the database | |
| From 4.0: `COPY ... FROM STDIN` and `COPY ... TO STDOUT`, in text and CSV formats | |
| From 4.0: `SAVEPOINT`, `SET LOCAL`, `SHOW` and `RESET` | |
| From 4.0: `PREPARE`, `EXECUTE` and `DEALLOCATE` statements | |
| From 4.0: window functions (`OVER (...)`) | |
| From 4.0: a SurrealDB token sent as the password | |
| From 4.0: [pgvector](#pgvector) columns, distance operators and `hnsw` indexes | |
| Before 4.0: SurrealQL as the default, with `USE` and `LET` persisting for the connection | |

Before SurrealDB 4.0, ANSI SQL translation, `pg_catalog` emulation (needed for some GUIs such as DBeaver, and for the psqlODBC ODBC driver, which queries `pg_type` while it connects) and `COPY` are not available.

## Start the listener

Bind a separate address from the HTTP server (default HTTP remains `127.0.0.1:8000`):

```bash
surreal db start --user root --pass secret \
  --postgres-bind 127.0.0.1:5432 \
  memory
```

Environment variable equivalent:

**Bash**

```bash
export SURREAL_POSTGRES_BIND=127.0.0.1:5432
surreal db start --user root --pass secret memory
```

**PowerShell**

```powershell
$env:SURREAL_POSTGRES_BIND = "127.0.0.1:5432"
surreal db start --user root --pass secret memory
```

Connect with `psql`:

```bash
psql "host=127.0.0.1 port=5432 user=root password=secret dbname=main/main"
```

The startup parameter **`database`** selects namespace and database as **`ns/db`** (a single slash). This mirrors choosing `Surreal-NS` and `Surreal-DB` on HTTP.

## Authentication

The Postgres listener supports two authentication mechanisms. Clients that offer [SCRAM-SHA-256](https://datatracker.ietf.org/doc/html/rfc7677) (most modern drivers and `psql`) use SASL challenge - response auth when the user has SCRAM verifier material stored. Otherwise the server falls back to the Postgres **cleartext password** message and verifies against the existing Argon2 hash via **`iam::verify::basic`**.

**MD5** (legacy Postgres auth) is not supported.

### SCRAM credentials (automatic)

When you define or update a system user with a plaintext password, SurrealDB derives and stores **SCRAM-SHA-256 verifier material** alongside the Argon2 hash. No extra DDL or `crypto::` functions are required.

```surql
DEFINE USER analyst ON DATABASE PASSWORD 'secret' ROLES VIEWER;
ALTER USER analyst ON DATABASE PASSWORD 'new-secret';
```

Users defined with **`PASSHASH` only** have no SCRAM material (no plaintext was available at definition time). They can sign in over HTTP/RPC with the hash path, but Postgres clients must use **cleartext password** auth for those users until you run **`ALTER USER … PASSWORD`** to set a plaintext password and regenerate SCRAM verifiers.

Root credentials created via **`surreal db start --user` / `--pass`** work on the Postgres port the same way as other IAM users once SCRAM material exists for that account.

### Tokens as passwords

_(since v4.0.0)_

A client can also send a token that SurrealDB issued, such as the token a [record user](/docs/reference/query-language/statements/define/access/record.md) receives from `signin`, as the password of a cleartext password exchange. The connection then runs as the user the token names, so table `PERMISSIONS` apply to a record user's queries. `SET ROLE` and the `request.jwt.claims` setting can only narrow the privileges of the session, and an attempt to widen them fails with **`42501`**.

### TLS

SCRAM avoids sending the password in the clear during authentication, but **TLS** (`--web-crt` and `--web-key`) is still recommended in production to encrypt the whole session. The server logs a warning when Postgres is served in plaintext without TLS configured.

Over TLS, the Postgres port advertises the `postgresql` application protocol (ALPN), which `psql` 17 and other clients built on libpq 17 or later offer, so they connect with `sslmode=require`. A client that offers only HTTP protocols, such as `h2` and `http/1.1`, is refused during the TLS handshake. Before SurrealDB 4.0, the port advertised the HTTP protocols of the web server, and a TLS connection from `psql` 17 or later failed with `tlsv1 alert no application protocol`.

Failed auth returns **`28P01`** without user enumeration.

## Coming from Postgres and SQL

From SurrealDB 4.0, a connection runs the [PostgreSQL dialect](#postgresql-sql), so queries written for PostgreSQL run against SurrealDB tables, with the differences that section lists. Before 4.0, the default query language is SurrealQL and the server does **not** translate ANSI SQL, as described under [SurrealQL before 4.0](#surrealql-before-40).

### Try it with `psql`

Start a local instance and connect (see [Start the listener](#start-the-listener)):

```bash
psql "host=127.0.0.1 port=5432 user=root password=secret dbname=main/main"
```

Create a table and add some rows:

```sql
CREATE TABLE person (id int PRIMARY KEY, name text, age int, city text);
INSERT INTO person (id, name, age, city) VALUES (1, 'Ada', 36, 'London'), (2, 'Bob', 28, 'Paris'), (3, 'Carl', 41, 'London');
```

Then query and change them as on PostgreSQL:

```sql
-- Filter, sort, limit
SELECT name, age, city FROM person WHERE age > 30 ORDER BY name LIMIT 10;

-- Aggregation
SELECT city, count(*) AS people FROM person GROUP BY city;

-- Window function
SELECT name, city, rank() OVER (PARTITION BY city ORDER BY age DESC) AS age_rank FROM person;

-- Update and delete
UPDATE person SET age = age + 1 WHERE city = 'London';
DELETE FROM person WHERE age < 18;
```

The table is a SurrealDB table, and each row is a record such as `person:1`, so a SurrealQL query over HTTP or an SDK reads the same data.

Of `psql`'s meta-commands, `\l`, `\dt`, `\dn`, `\du` and `\di` list the databases, tables, schemas, roles and indexes. `\d` with a table name does not complete yet, because one of the queries `psql` sends for it calls a set-returning function in `FROM`.

### Client tools

Other PostgreSQL client tools work to different degrees:

- **`pg_isready`** reports the listener as accepting connections, over plaintext and TLS.
- **`pgbench`** runs custom scripts given with `-f`. `pgbench -i` runs every initialisation step except the data load, which fails because `pgbench` sends `COPY ... FROM STDIN` without a column list. Leave that step out with `pgbench -i -I dtpv`. For these steps, `CREATE TABLE ... WITH (...)` storage parameters are accepted and discarded, `VACUUM` and `ANALYZE` answer with their command tags and do nothing, and `TRUNCATE` takes a list of tables.
- **`pg_dump`** does not work yet, because the server does not answer all of its catalog queries.
- **ORMs and migration tools** that reset a database with `DROP SCHEMA public CASCADE`, such as Drizzle, or that read `pg_matviews` at startup, such as TypeORM, can run those statements, as the [PostgreSQL SQL](#postgresql-sql) section describes.

### BI and analytics tools

Many BI products (Metabase, Superset, Grafana Postgres data sources, and similar) can add a **Postgres** connection with host, port, user, password, and database name. Use the same **`database=ns/db`** form as `psql`.

| BI workflow | Works now? | Notes |
| --- | --- | --- |
| **Native / custom SQL** query editor | Yes | From 4.0, write PostgreSQL SQL in the tool's SQL box. Before 4.0, write SurrealQL. |
| **`psql`-style exploration** | Yes | Ad hoc `SELECT`, `GROUP BY` and filters. |
| **Drag-and-drop chart builder** | From 4.0, partly | The SQL a tool generates runs in the PostgreSQL dialect, unless it uses a feature listed under [Not yet supported](#not-yet-supported). Before 4.0, generated SQL does not run. |
| **Schema browser / table picker** | From 4.0 | The `pg_catalog` and `information_schema` relations list the tables of the database and their declared columns, so a table picker shows them. Before 4.0, the relations are not available. |
| **Paste arbitrary Postgres SQL** | From 4.0 | Queries run in the PostgreSQL dialect. Before 4.0, there is no SQL-to-SurrealQL translation: similar-looking SELECTs may work, and Postgres-specific syntax will not. |

Configure the Postgres data source with your SurrealDB host and `--postgres-bind` port, set database to `your_ns/your_db`, then open the tool's **SQL** or **Native query** mode.

## Query languages

### PostgreSQL SQL

_(since v4.0.0)_

Every new connection runs the PostgreSQL dialect of SQL, so that queries written for PostgreSQL, and the SQL that drivers, ORMs and BI tools generate, run against SurrealDB tables. A connection that has switched to [ISO GQL](#iso-gql-optional-dialect) switches back with `SET dialect = 'pgsql'`. The names `postgres`, `postgresql` and `sql` select the same dialect. `SET dialect = 'surrealql'`, `SET dialect = 'mysql'`, and the same names in the `options` startup parameter, are refused with **`0A000`**.

```sql
CREATE TABLE person (id int PRIMARY KEY, name text, age int);
INSERT INTO person (id, name, age) VALUES (1, 'Ada', 36), (2, 'Bob', 28);
SELECT name, age FROM person WHERE age > 30 ORDER BY name;
```

The dialect is allowed by the same `postgres` route capability as the connection itself, and needs no experimental capability. Some points differ from SurrealQL or from PostgreSQL:

- **Record IDs are keys.** In a result, every record ID is sent as its key, as a SQL client expects a primary or foreign key to be: `person:1` is sent as `1`. The `id` column, a `WHERE id = 1` condition, joins on keys, and a write to a field defined as `record<T>` all use the key.
- **SQL functions only.** Queries can call the PostgreSQL functions that SurrealDB implements, but no SurrealQL function, such as `http::get` or a `fn::` function. The implemented functions are tested against the answers that PostgreSQL 18 gives for the same arguments, and include the keyword forms such as `extract(year FROM d)`, `position('b' IN s)`, `substring(s FROM 2 FOR 3)` and `overlay`. An error that a function raises is reported with PostgreSQL's SQLSTATE, as listed under [Error codes](#error-codes).
- **Schema inspection.** `information_schema.tables`, `information_schema.columns`, `pg_class`, `pg_attribute`, `pg_constraint`, `pg_index` and the other catalog relations that drivers, ORMs and schema browsers read list the tables of the database, in the schema `public`. A column is listed when the table declares it, so a table without declared columns lists only `id`. `pg_matviews` has no rows, because SurrealDB has no materialised views.
- **The schema `public`.** The schema `public` is the selected database, and `public.t` is the table `t`. `DROP SCHEMA public CASCADE` removes every table of the database in one all-or-nothing statement, views before the tables they read, together with the sequences of their `serial` columns, and needs the role that `DROP TABLE` of each table needs. Without `CASCADE`, it fails with **`0A000`** when the database holds a table, where PostgreSQL reports `2BP01`. No other schema exists, so `DROP SCHEMA` of another name fails with **`42P01`** unless it has `IF EXISTS`, and `pg_catalog`, `information_schema` and `pg_toast` are refused with **`0A000`**.
- **Sized number columns.** `smallint`, `integer`, `bigint`, `real` and `double precision` columns define fields of the [sized number](/docs/reference/query-language/language-primitives/data-types/numbers.md#sized-numbers) types `i16`, `i32`, `int`, `f32` and `float`, so an `int` column is an `i32` field and the catalog lists it as `integer` (`int4`). A value out of range for the column, such as `40000` for a `smallint`, is refused with **`42804`**. An `int` primary key makes the record ID key an `i32` as well.
- **Declared column types.** `CREATE TABLE` records the type of each column as the field's [`SQL TYPE`](/docs/reference/query-language/statements/define/field.md#declaring-a-sql-type), and every write is checked against it. A value too long for a `varchar(n)` column is refused with **`22001`**, and a `numeric(p,s)` column rounds a value to its scale. A `numeric` column with a precision above 28, or with none, defines a [`bigdecimal`](/docs/reference/query-language/language-primitives/data-types/numbers.md#big-decimal-numbers) field. `CREATE TYPE ... AS ENUM`, `CREATE DOMAIN` and `CREATE TYPE ... AS (...)` each define a [named type](/docs/reference/query-language/language-primitives/data-types/named-types.md) with `DEFINE TYPE`: an enum becomes a union of strings, and a column of that type orders its values in declaration order; a domain becomes a type over its base type with one `ASSERT` for each `CHECK`; and a composite type becomes an object type. `ALTER DOMAIN` redefines the type, and `DROP DOMAIN` and `DROP TYPE` remove it.
- **Foreign keys.** A `REFERENCES` clause or a `FOREIGN KEY` constraint, in `CREATE TABLE` or in `ALTER TABLE ... ADD FOREIGN KEY`, makes the column a [record link](/docs/reference/query-language/statements/define/field.md#defining-a-reference) to the other table. A write whose key names no record in that table fails with **`23503`**. `ALTER TABLE ... DROP CONSTRAINT` takes the names that `pg_constraint` lists, such as `note_person_id_fkey`.
- **Generated keys.** A `serial` or `bigserial` column draws its numbers from a sequence. A table created again after `DROP TABLE` numbers its rows from 1. `TRUNCATE` keeps the count, as PostgreSQL's default `CONTINUE IDENTITY` does, and `TRUNCATE ... RESTART IDENTITY` starts it again from 1. `RESTART IDENTITY` defines the sequence again, so a user who may delete records but not define a sequence, such as an editor, is refused it.
- **Window functions.** `row_number`, `rank`, `dense_rank`, `percent_rank`, `cume_dist`, `ntile`, `lag`, `lead`, `first_value`, `last_value` and `nth_value` run over a window, and so do `sum`, `avg`, `count`, `min` and `max`. A window takes `PARTITION BY`, `ORDER BY` with `NULLS FIRST` or `NULLS LAST`, a named `WINDOW` clause, a `ROWS` frame, and a `RANGE` frame bounded by `UNBOUNDED` or `CURRENT ROW`. A window can read a `GROUP BY` result, as in `rank() OVER (ORDER BY sum(n) DESC)`. A partition that needs more memory than the query may use is written to temporary storage.
- **Prepared statements in SQL.** `PREPARE name [(types)] AS statement`, `EXECUTE name(args)` and `DEALLOCATE [PREPARE] name | ALL` work on the connection. A name already in use fails with **`42P05`**, and an unknown name with **`26000`**.
- **Bulk load and export.** `COPY ... FROM STDIN` and `COPY ... TO STDOUT` move rows in the text and CSV formats. Name the columns in `COPY ... FROM STDIN`, for example `COPY person (id, name) FROM STDIN`.
- **Typed literals.** Write a typed value as a cast, such as `'2026-01-01'::date`. A plain string is not converted to a `date` column, and the `DATE '2026-01-01'` form does not parse.
- **Declared columns.** A table that declares its columns, with `CREATE TABLE` or `DEFINE TABLE ... SCHEMAFULL`, is checked as PostgreSQL checks a table. A column that the table does not declare fails with **`42703`**, `SELECT *` returns the declared columns, with `NULL` for an optional column that a record does not hold, and drivers are told the declared types. `*` lists `id` first and then the other columns by name, not in the order they were declared. These checks apply to users who may view table definitions. For a record user, a column that the table does not declare reads as `NULL`, and `*` lists the fields that the records hold.
- **Tables without declared columns.** A schemaless table has no fixed list of columns, so a column that none of its records hold reads as `NULL` instead of failing. In a join, a column named without its table is read from the first table of the join that has a value for it, and a name that two tables hold is not refused as ambiguous. Qualify the column with its table, as in `b.w`, to choose the table. `id` without a table is always the first table's `id`. In a `FROM` clause of more than 64 tables, a column named without its table fails with **`0A000`** and must be qualified.
- **`t.*`.** `SELECT u.* FROM users u` returns what `SELECT *` returns, over a table or a derived table. In a join, `t.*` of a table whose columns are not declared fails with **`0A000`**.
- **Table aliases.** A table given an alias is reachable only by the alias, as in PostgreSQL, so `SELECT users.id FROM users u` fails with **`42P01`**.
- **`LIKE` patterns.** A pattern is read the same way whether it is written in the query, bound as a parameter (`LIKE $1`) or read from a column. `\%` and `\_` match a literal `%` and `_`, and `%` and `_` also match a line break. A pattern that is not written in the query is converted with the `string::replace` and `string::concat` functions, so an instance that [restricts functions](/docs/learn/security/authorization/capabilities.md#functions) must allow those two. A pattern too long to compile fails with `LIKE pattern is too complex`.
- **Subqueries used as a value.** A subquery used as a single value, as in `WHERE dept = (SELECT id FROM dept)`, fails when it returns more than one row, and fails when it selects more than one column. Add `ORDER BY ... LIMIT 1` or a narrower `WHERE` to choose one row. The error is reported under SQLSTATE **`P0001`**, not PostgreSQL's `21000`. `IN`, `= ANY` and `EXISTS` accept any number of rows. A subquery can read a column of any query that encloses it, including a subquery nested in another subquery that reads a column of the outermost query.
- **Schema changes are all or nothing.** `CREATE TABLE`, `DROP TABLE` of several tables and `ALTER TABLE` with several actions are each answered once. If one part fails, nothing of the statement is kept, so `DROP TABLE a, missing` fails and keeps `a`.
- **Nesting limits.** A `FROM` clause that lists more than about 120 tables, nests a join in more than about 120 parentheses, or nests subqueries more than about 60 deep is refused with **`42601`** `parser recursion depth limit exceeded`.

#### Not yet supported

The PostgreSQL dialect refuses or differs from PostgreSQL in these places:

- Recursive common table expressions and `FULL OUTER JOIN` are refused with **`0A000`** (`feature_not_supported`). Non-recursive `WITH` queries work.
- In a window function, `GROUPS` frames, `EXCLUDE` other than `EXCLUDE NO OTHERS`, `RANGE` frames with a numeric offset, frame offsets that are not literals, `lag` and `lead` offsets and `nth_value` positions that read the row, and `DISTINCT` inside the call are refused with **`0A000`**. A `PARTITION BY` or `ORDER BY` key whose value can change when it is evaluated again, such as `random() < 0.5`, makes the query fail.
- `INTERSECT ALL` and `EXCEPT ALL` are refused with **`0A000`**. So are `INTERSECT` and `EXCEPT` with `*` in either query, with a computed column that has no alias inside a derived table, inside a subquery used as a value or with `IN` or `EXISTS`, and in a chain of three or more queries.
- An `ORDER BY` inside an aggregate call, as in `string_agg(name, ',' ORDER BY id)`, does not parse, and fails with **`42601`**.
- `CREATE FUNCTION` does not parse, and fails with **`42601`** (`syntax_error`).
- A `LATERAL` subquery runs in a `CROSS JOIN` or an inner join when it is a `SELECT` of expressions with no other clause, as in `CROSS JOIN LATERAL (SELECT u.id + 1 AS n) d`. A `LEFT JOIN LATERAL`, and a lateral subquery with a `FROM` clause, a `WHERE` or an aggregate, are refused with **`0A000`**.
- A write that uses a clause the dialect cannot run, such as `UPDATE ... FROM`, `DELETE ... USING`, a `WITH` clause, a subquery in its condition or values, or an `INSERT ... SELECT` that reads from a join or a subquery, is refused with **`0A000`** before it changes anything.
- In an `INSERT ... SELECT`, each item of the query is written to the column of the same name, not to the column at its position, so give an item an alias where its name differs, as in `INSERT INTO users (id, name) SELECT id + 10 AS id, title AS name FROM posts`.
- `CREATE VIEW` over one table with a projection and a filter holds the rows that pass the filter, and stays up to date after every write to the table. A column list is ignored, and a view over a join fails. `CREATE MATERIALIZED VIEW` does not parse.
- The array operators `@>`, `<@` and `&&`, and the `ARRAY[...]` constructor, are refused with **`0A000`**.
- The `||` and `LIKE` operators are refused with **`0A000`** in an `INSERT`, `UPDATE` or `DELETE`, including in its `WHERE` clause, so `DELETE FROM t WHERE name LIKE 'a%'` fails before it deletes anything. They work in a `SELECT`. Functions, such as `substring`, `ltrim`, `concat` and `extract`, work in both, apart from the [pgvector](#pgvector) distance functions.
- `generate_series` takes two arguments and works in the select list, as in `SELECT generate_series(1, 5)`. The form with a step, and `generate_series` or another set-returning function in `FROM`, are refused.
- A column in the select list or in `HAVING` that is neither grouped nor aggregated is not refused, and in the select list it returns the values of its group as an array. An aggregate in `WHERE` is not refused either.
- `LIKE ... ESCAPE` is refused. A trailing backslash in a pattern matches a backslash.
- `ALL` and `ANY` comparisons over a subquery that has `GROUP BY`, `HAVING`, `LIMIT` or `OFFSET`, or is a set operation, are refused. `UNION` inside a subquery used as a value can report more than one row where PostgreSQL reports one.
- `t.*` inside a function call (`count(t.*)`, `row_to_json(t.*)`), in `RETURNING`, in `CREATE VIEW`, and beside another item in the query of an `INSERT` is refused.
- `CREATE SCHEMA` and `ALTER TABLE ... RENAME COLUMN` answer with their command tag but change nothing. `DROP VIEW` answers with the tag `DROP TABLE`.
- An arithmetic expression over two fields with no declared type, such as `a + b`, is announced as `bigint`, so a result that is a float fails to encode.

### pgvector

_(since v4.0.0)_

The PostgreSQL dialect accepts the column type, distance operators and `hnsw` indexes of the [pgvector](https://github.com/pgvector/pgvector) extension, so an application written for pgvector can store embeddings in SurrealDB and search them by distance. The vectors are stored as SurrealDB arrays, and an `hnsw` index is a SurrealDB [HNSW index](/docs/reference/query-language/statements/define/indexes.md#hnsw-hierarchical-navigable-small-world), so SurrealQL queries can read and search the same data.

```sql
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE item (id int PRIMARY KEY, embedding vector(3));
CREATE INDEX item_embedding ON item USING hnsw (embedding vector_l2_ops);
INSERT INTO item (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]'), (3, '[1,0,0]');
SELECT id FROM item ORDER BY embedding <-> '[1,2,3]' LIMIT 2;
```

`CREATE EXTENSION vector` is accepted and defines nothing, so a migration that runs it first does not fail. Any other extension fails with **`58P01`**.

**Columns.** A `vector(n)` column is a field of type `array<float, n>`, and a column of that type defined in SurrealQL is a vector column as well. The dimensions are required: a `vector` column without `(n)` is refused with **`0A000`**, and a vector can have at most 16,000 dimensions. A value written to a vector column as text, as a parameter or as a cast is read as the text form `[1,2,3]`. `'[1,2,3]'::vector(3)` casts a string in a query. `INSERT ... SELECT` converts the text it selects into a vector column, and refuses a vector of the wrong dimensions with **`42804`**. A request that creates a table and then writes to a vector column of an existing table, such as `CREATE TABLE audit (...); INSERT INTO item ...`, reads the text as a vector as well. Drivers see the column as the `vector` type, in the text and binary formats, so the pgvector packages for psycopg, asyncpg and node-postgres find the type as they do on PostgreSQL.

**Distances.** Each operator has a function of the same meaning:

| Operator | Function | Distance |
| --- | --- | --- |
| `<->` | `l2_distance(a, b)` | Euclidean |
| `<=>` | `cosine_distance(a, b)` | One minus the cosine similarity |
| `<#>` | `vector_negative_inner_product(a, b)` | The negated inner product |
| `<+>` | `l1_distance(a, b)` | Manhattan |

`inner_product(a, b)` returns the inner product itself. A distance over `NULL` is `NULL`, and a distance between vectors of different dimensions fails with **`22000`**.

**Indexes.** `CREATE INDEX ... USING hnsw (column opclass)` defines an HNSW index. The operator class chooses the distance that the index ranks by: `vector_l2_ops` (Euclidean), `vector_cosine_ops` (cosine), `vector_ip_ops` (inner product) or `vector_l1_ops` (Manhattan). `WITH (m = 16, ef_construction = 64)` sets the graph parameters, with pgvector's defaults and limits: `m` from 2 to 100, `ef_construction` from 4 to 1,000 and at least twice `m`. An index covers at most 2,000 dimensions.

**Nearest-neighbour search.** `ORDER BY column <op> value LIMIT k`, with an optional `OFFSET`, over one table is a nearest-neighbour search. SurrealDB answers it from the HNSW index when the column has exactly one HNSW index and that index ranks by the same distance as the operator, and with an exact search of the table otherwise. Any other ordering by a distance sorts every row. `EXPLAIN SELECT` shows which plan a query uses: `KnnScan` for the index and `KnnTopK` for the exact search. A row whose vector is `NULL` is not returned by a nearest-neighbour search, because the index does not hold it.

#### Not supported

- The `halfvec`, `sparsevec` and `bit` types, binary quantisation and iterative index scans.
- `ivfflat` indexes, and `hnsw` indexes that are partial, unique, over more than one column or over an expression. Each is refused with **`0A000`**.
- `hnsw.ef_search`. `SET hnsw.ef_search` is accepted, but the index search always uses an `ef` of 40, or `k` when it is larger.
- A bound limit, such as `LIMIT $1`. `k` must be written in the query for the index to be used, and with a bound limit the query sorts every row.
- A distance operator or function in an `INSERT`, `UPDATE` or `DELETE`, which is refused with **`0A000`**. Use them in a `SELECT`.
- `COPY ... FROM` into a vector column, which does not convert text to a vector.
- An index created earlier in the same request is not used by a later `SELECT` in that request, which searches exactly.
- `DROP EXTENSION`, `vector_dims`, `vector_norm`, `l2_normalize`, arithmetic on vectors and `avg` over a vector column.

### ISO GQL (optional dialect)

GQL is **not** the default and is **not** what most Postgres users expect from a “Postgres” port. It is available so the **same connection** can run [ISO GQL](/docs/learn/querying/gql/overview.md) when you opt in - the same engine as [`POST /gql`](/docs/reference/protocols/http-protocol.md#gql), with results encoded as Postgres rows instead of JSON.

**When GQL over Postgres is useful:**

- You already use a **Postgres driver or pool** and want graph queries **without a second HTTP client**.
- A tool or script **only speaks Postgres** but you want to try GQL from it (for example `psql` with `SET dialect = 'gql'`).
- You standardise on **one TCP port and auth path** for both SQL and GQL in internal tooling.

**When to use HTTP or RPC instead:** public APIs, browser clients, typed GQL variables over RPC, or anything that fits the JSON envelope and headers of [`POST /gql`](/docs/reference/protocols/http-protocol.md#gql) more naturally.

GQL is enabled by default from 3.3.0, the same release as the Postgres listener, so the server needs no experimental capability:

```bash
surreal db start --user root --pass secret \
  --postgres-bind 127.0.0.1:5432 \
  memory
```

Select GQL for the session:

```sql
-- At connect time (many drivers pass startup options):
-- options=-c dialect=gql

-- Or after connect:
SET dialect = 'gql';
MATCH (n:person) RETURN n.name AS name ORDER BY name \g

SET dialect = 'pgsql';
SELECT * FROM person;
```

A GQL statement must not end with a semicolon. In `psql`, send it with `\g` instead of `;`, as in `MATCH (n:person) RETURN n.name \g`.

> [!WARNING]
> **GQL inside an interactive transaction** (`BEGIN` … `COMMIT`) is **rejected**. Finish or roll back the transaction before switching to GQL, or use SQL within the transaction.

### SurrealQL before 4.0

Before SurrealDB 4.0, every new connection uses the **SurrealQL** dialect unless it selects GQL, and `SET dialect = 'surrealql'` switches back to it. Send SurrealQL as you would on [`POST /sql`](/docs/reference/protocols/http-protocol.md):

```sql
CREATE person SET name = 'A';
SELECT * FROM person;
```

`USE ns/db` and `LET` persist for the lifetime of the connection, as on other surfaces. From 4.0, `SET dialect = 'surrealql'` is refused with **`0A000`**, and SurrealQL runs over HTTP, RPC and the SDKs.

## Results and typing

In GQL, and in SurrealQL before 4.0, the result shape follows Postgres tabular conventions:

- **Object rows** (typical `SELECT` / `RETURN` output) become **one column per object key**, with types inferred from the values and promoted across rows (for example `int` and `float` widen to `float8` / `numeric`).
- **Scalars and non-object arrays** become a single **`value`** column.
- SurrealDB types map to Postgres OIDs where possible (`bool`, `int8`, `float8`, `numeric`, `text`, `timestamptz`, `interval`, `uuid`, `bytea`, `jsonb`). Record IDs and similar values encode as **text**; nested structures encode as **jsonb**.

In the [PostgreSQL dialect](#postgresql-sql), results follow PostgreSQL more closely:

- A command that returns no rows, such as `CREATE TABLE`, `DROP TABLE`, `TRUNCATE` or an `INSERT` without `RETURNING`, is answered with its command tag alone (`CREATE TABLE`, `INSERT 0 3`), as PostgreSQL does. A query string that holds no statement, such as `;` or only comments, is answered with an empty query response.
- Each column of a `SELECT` keeps its own value, including columns that share a name, as in `SELECT a.k, b.k FROM a JOIN b ON a.k = b.k`, and columns with no name, as in `SELECT 1, 'a'`.
- A result column is announced as a type that all its values have. `floor` and `ceil` take the type of their argument. `CASE`, `COALESCE`, `GREATEST` and `LEAST` take the widest number type among their alternatives, in PostgreSQL's order (`smallint`, `integer`, `bigint`, `numeric`, `real`, `double precision`), or `text` when the alternatives are not all numbers. A `NULL` alternative does not change the type, and a field with no declared type counts as `text`. The type can differ from PostgreSQL's: `floor(1.5)` is a float here and `numeric` in PostgreSQL, and a `CASE` over a number and a text answers text where PostgreSQL refuses it.

### Extended query protocol and prepared statements

Drivers using **Parse / Bind / Execute** (extended protocol) get a hybrid typing model:

- **Driver-prepared** statements (no eager execute) advertise a single **`jsonb`** column when SurrealDB cannot know the columns before the statement runs. A prepared `SELECT` over a table that declares its columns is described with those columns and their types.
- **`prepare_typed` / portal describe** paths that execute eagerly return **true typed columns** matching the result.

Postgres positional parameters **`$1`, `$2`, …** are bound in order. Before 4.0, they are rewritten to the SurrealQL parameters **`$_1`, `$_2`, …** and bound as **`_1`, `_2`, …** in the session.

## Interactive transactions

Standalone **`BEGIN`**, **`COMMIT`**, and **`ROLLBACK`** open an interactive transaction on the connection. Ready-for-query status bytes follow Postgres semantics (`I` idle, `T` in transaction, `E` failed transaction). After an error in a transaction block, further commands receive **`25P02`** until `COMMIT` or `ROLLBACK`.

From SurrealDB 4.0, `BEGIN ISOLATION LEVEL` is applied to the transaction, and so is `BEGIN READ ONLY`. `SERIALIZABLE` opens a [serializable transaction](/docs/learn/querying/concepts-and-guides/transactions.md#serializable-isolation) on the storage engines that offer one, and fails with **`0A000`** (`feature_not_supported`) on the others. `REPEATABLE READ` runs at snapshot isolation, as it does in PostgreSQL. `READ COMMITTED` and `READ UNCOMMITTED` are refused with **`0A000`**, because a snapshot never sees commits made while the transaction runs, and the same applies when `default_transaction_isolation` is set to either of them. A plain `BEGIN` runs at snapshot isolation.

In the PostgreSQL dialect, `SAVEPOINT`, `RELEASE SAVEPOINT` and `ROLLBACK TO SAVEPOINT` work inside a transaction block, and `SET LOCAL` settings are undone when the transaction or savepoint is rolled back.

**Divergence from Postgres:** SurrealDB **auto-commits each top-level statement** outside an explicit `BEGIN` … `COMMIT` block, and in a simple query of several statements outside a transaction block, a failing statement does not stop the statements after it. For all-or-nothing behaviour, wrap statements in an explicit transaction.

## Error codes

A failed statement is reported with the SQLSTATE that PostgreSQL uses for the same kind of failure, over the simple and the extended query protocol. Drivers and ORMs use the SQLSTATE to decide what to do, for example to treat a duplicate key differently from a syntax error. The message text is SurrealDB's own.

| SQLSTATE | Condition | Reported when |
| --- | --- | --- |
| `23505` | `unique_violation` | A write conflicts with a `UNIQUE` index or a primary key. |
| `23502` | `not_null_violation` | A write gives `NULL`, or no value, to a field that requires one. |
| `23503` | `foreign_key_violation` | A write names a key that the table of a foreign key does not hold. |
| `42804` | `datatype_mismatch` | A write gives a field a value of another type. SurrealDB refuses text for a number field, even `'5'`, where PostgreSQL reports `22P02` or accepts it. |
| `22001` | `string_data_right_truncation` | A write gives a text column a value longer than its declared type allows, such as `varchar(5)`. |
| `22011` | `substring_error` | `substring` is given a negative length. |
| `22012` | `division_by_zero` | A number is divided by zero, with `/` or `%`. |
| `2201E` | `invalid_argument_for_logarithm` | `ln` or `log` is given zero or a negative number. |
| `22023` | `invalid_parameter_value` | A function is given an invalid argument, such as a field position of `0` for `split_part` or an unknown unit for `date_trunc`. |
| `42601` | `syntax_error` | The statement cannot be parsed, or exceeds a nesting limit. |
| `42704` | `undefined_object` | `ALTER TABLE ... DROP CONSTRAINT` names a constraint that does not exist. |
| `42P01` | `undefined_table` | The statement names a table that does not exist, or a table qualifier missing from `FROM`. |
| `42703` | `undefined_column` | The statement names a column that a table with declared columns does not have. |
| `42702` | `ambiguous_column` | A column name could belong to more than one table. |
| `42883` | `undefined_function` | The statement calls a function that does not exist, or with the wrong arguments. |
| `42P10` | `invalid_column_reference` | A `GROUP BY` or `ORDER BY` position is not in the select list. |
| `42803` | `grouping_error` | An aggregate is used in `GROUP BY`. |
| `42P20` | `windowing_error` | A window function is used where it is not allowed, such as in `WHERE`. |
| `42501` | `insufficient_privilege` | The user may not run the statement. |
| `0A000` | `feature_not_supported` | The statement uses a feature that SurrealDB does not support. |
| `25P02` | `in_failed_sql_transaction` | A command is sent after an error in a transaction block. |
| `28P01` | `invalid_password` | Authentication fails. |
| `40001` | `serialization_failure` | The transaction conflicts with another transaction. Retry it. |
| `P0001` | `raise_exception` | `THROW` raises an error, or a subquery used as a value returns more than one row. |
| `XX000` | `internal_error` | Any other failure. |

The codes for a constraint or a value refused by a write apply in every dialect. The codes for a table, column, function or position that a statement names wrongly come from the [PostgreSQL dialect](#postgresql-sql).

## Capabilities and security

Connections are gated like other query surfaces:

- **`RouteTarget::Postgres`** - controlled via [`--allow-http`](/docs/reference/cli/db/commands/start.md) / [`--deny-http`](/docs/reference/cli/db/commands/start.md) with the route name **`postgres`** (the capability helper is shared with HTTP route names).
- **Arbitrary query** - subject to [`--allow-arbitrary-query`](/docs/learn/security/authorization/capabilities.md#arbitrary-queries) / [`--deny-arbitrary-query`](/docs/learn/security/authorization/capabilities.md#arbitrary-queries) for `guest`, `record`, and `system` users.
- **GQL** - enabled by default from 3.3.0, so no experimental capability is required.

Authentication is described in [Authentication](#authentication). Resource limits include a connection cap, startup/auth timeout, message size limits, and prepared-statement / portal caps.

## Comparison with other surfaces

| Surface | Transport | Default language | Typical client |
| --- | --- | --- | --- |
| [`POST /sql`](/docs/reference/protocols/http-protocol.md) | HTTP | SurrealQL | `curl`, scripts |
| [`POST /gql`](/docs/reference/protocols/http-protocol.md#gql) | HTTP | ISO GQL | `curl`, HTTP clients |
| [RPC](/docs/reference/protocols/rpc-protocol.md) | HTTP / WebSocket | SurrealQL (+ RPC methods, including [`pgsql`](/docs/reference/protocols/rpc-protocol.md#pgsql) from 4.0) | Official SDKs |
| **Postgres wire** | TCP (Postgres v3) | PostgreSQL SQL (SurrealQL before 4.0) | `psql`, JDBC, `tokio-postgres`, … |
| [MySQL wire](/docs/reference/protocols/mysql-protocol.md) (from 4.0) | TCP (MySQL protocol) | MySQL SQL | `mysql`, MySQL drivers |

## Related pages

- [SurrealQL reference](/docs/reference/query-language.md): statements, types, and clauses
- [MySQL wire protocol](/docs/reference/protocols/mysql-protocol.md): the equivalent listener for MySQL clients, which can also select the PostgreSQL dialect
- [PostgreSQL and MySQL compatibility matrix](/docs/reference/protocols/sql-compatibility.md): the two dialects compared feature by feature
- [GQL overview](/docs/learn/querying/gql/overview.md): ISO GQL concepts and wire surfaces
- [GQL via HTTP](/docs/learn/querying/gql/via-http.md): enable GQL and run queries with `curl`
- [`DEFINE USER`](/docs/reference/query-language/statements/define/user.md#scram-credentials-for-postgres-clients): SCRAM verifier material for Postgres clients
- [Capabilities](/docs/learn/security/authorization/capabilities.md): lock down arbitrary queries and routes
- [`surreal db start`](/docs/reference/cli/db/commands/start.md): CLI reference including `--postgres-bind`
- [MySQL wire protocol](/docs/reference/protocols/mysql-protocol.md): connect with MySQL clients and drivers
