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

# PostgreSQL and MySQL compatibility matrix

Feature-by-feature matrix of what the PostgreSQL and MySQL dialects run in SurrealDB 4: queries, writes, DDL, transactions, types, functions, catalogs, pgvector and error codes.

_(since v4.0.0)_

> [!NOTE]
> SurrealDB is working towards full compatibility with PostgreSQL and MySQL. Coming releases will close the gaps that this matrix lists, and the matrix is updated with each release.

This page compares, feature by feature, what SurrealDB 4 runs for PostgreSQL clients and for MySQL clients. Both listeners run a defined subset of their dialect through the same query planner as SurrealQL, over the same tables. A statement outside the subset is refused with the error code of the original database, before it changes any data. The [Postgres wire protocol](/docs/reference/rest-api/postgres-protocol.md) and [MySQL wire protocol](/docs/reference/rest-api/mysql-protocol.md) pages describe each listener in full, and each row below links back to them by name.

The matrix applies to the PostgreSQL dialect, which every connection to the Postgres listener runs, and to the MySQL dialect, which every connection to the MySQL listener runs. The functions of the PostgreSQL dialect are tested against PostgreSQL 18, and the functions of the MySQL dialect against MySQL 8.4.

## How to read the matrix

Each cell starts with a verdict, followed by the detail:

| Verdict | Meaning |
| --- | --- |
| **Supported** | The statement runs as it does in the original database, apart from the differences the cell lists. |
| **Partial** | Some forms run and others are refused. The cell lists both. |
| **Differs** | The statement runs, and the result or the behaviour is not the one the original database gives. |
| **Refused** | The statement fails with the error code the cell names, before it changes any data. |
| **Not supported** | The dialect does not run the statement. |
| **Not documented** | The protocol pages do not cover the feature, so the matrix makes no claim about it. |
| **Not applicable** | The original database has no such feature. |

## Connecting

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Protocol | **Supported**. Protocol v3.0 with the simple and extended query flows, prepared statements, interactive transactions, cancellation and optional TLS. Available since 3.3. | **Supported**. Plain and prepared statements, statements sent several at a time, and the statements that drivers send while they connect. New in 4.0. |
| Enabling the listener | **Opt-in**. `--postgres-bind` or `SURREAL_POSTGRES_BIND`, on its own address. | **Opt-in**. `--mysql-bind` or `SURREAL_MYSQL_BIND`, on its own address. |
| Default language | **PostgreSQL SQL**. From 4.0, every connection runs the PostgreSQL dialect, and `SET dialect = 'gql'` switches to ISO GQL. SurrealQL is not available over this listener: `SET dialect = 'surrealql'` is refused with `0A000`. Before 4.0, a connection runs SurrealQL. | **MySQL SQL**. `SET dialect = 'gql'` switches to ISO GQL. SurrealQL and the PostgreSQL dialect are not available over this listener: `SET dialect = 'surrealql'` and `SET dialect = 'pgsql'` fail with `1045`. |
| Namespace and database | **Supported**. The database name is `ns/db`, with a single slash. | **Supported**. The database name is `ns/db`, with a single slash, and `USE ns/db` changes both on an open connection. |
| Authentication | **Supported**. `SCRAM-SHA-256` when the user has verifier material, otherwise a cleartext password. A SurrealDB token can be sent as the password. MD5 is not supported, and a failed sign-in returns `28P01`. | **Supported**. `caching_sha2_password`, `mysql_native_password` and `mysql_clear_password`. `caching_sha2_password` works over TLS, and over plaintext with the server's RSA public key, so the `mysql` client needs `--get-server-public-key` over plaintext. `mysql_native_password` is verified directly only for a user who signed in earlier since the server started, and otherwise switches to `mysql_clear_password`. A sign-in that sends the password as clear text needs TLS or a loopback address. |
| TLS | **Supported**. The listener uses the certificate and key of the HTTP server, and advertises the `postgresql` ALPN protocol, so libpq 17 clients connect with `sslmode=require`. | **Supported**. The listener uses the certificate and key of the HTTP server. The default authentication method of MySQL 8 clients works over TLS. |
| Record IDs in results | **Differs**. A record ID is sent as its key, as a SQL client expects a primary key to be: `person:1` is sent as `1`. | **Differs**. A record ID is sent as its key: `person:1` is sent as `1`. |
| Client tools | **Partial**. `pg_isready` works, and `pgbench` runs custom scripts and the initialisation steps except the data load. In `psql`, `\l`, `\dt`, `\dn`, `\du` and `\di` work, and `\d` with a table name does not complete yet. `pg_dump` does not work yet. | **Partial**. `mysqladmin` and `mysqlslap` run. `mysqldump` runs with `--no-tablespaces` and `--skip-lock-tables`, and its structure section holds the SurrealQL definition, so pass `--no-create-info` to dump the rows only. |

## Queries

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Joins | **Partial**. Joins run, including `LEFT JOIN` and `LATERAL` subqueries. `FULL OUTER JOIN` is refused with `0A000`. A `LEFT JOIN LATERAL` takes only `ON true`, and a lateral subquery that reads a column of a relation to its left other than the first is refused with `0A000`. | **Partial**. `SELECT` with joins runs, with `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY` and `LIMIT`. A `LATERAL` subquery fails with `1064`. |
| Aggregates and grouping | **Partial**. Aggregates, `GROUP BY` and `HAVING` run, and an aggregate takes an `ORDER BY` inside its call. Two aggregates of one query with different `ORDER BY` clauses are refused with `0A000`. A column that is neither grouped, aggregated nor fixed by the group is not refused, and in the select list it returns the values of its group as an array. An aggregate in `WHERE` fails with `42803`. | **Partial**. Aggregate functions, `GROUP BY` and `HAVING` run. An aggregate in `WHERE` fails with `1111`, and an `ORDER BY` inside `GROUP_CONCAT` fails with `1064`. |
| Common table expressions | **Partial**. Non-recursive `WITH` queries run. Recursive common table expressions are refused with `0A000`. | **Partial**. Non-recursive `WITH` queries run. Recursive common table expressions are refused with `1235`, before the statement changes any data. |
| Window functions | **Partial**. Ranking, offset and aggregate window functions run, with `PARTITION BY`, `ORDER BY`, a named `WINDOW` clause, `ROWS` frames and `RANGE` frames bounded by `UNBOUNDED` or `CURRENT ROW`. `GROUPS` frames, `EXCLUDE`, `RANGE` frames with a numeric offset and `DISTINCT` inside the call are refused with `0A000`. A window function in `WHERE` is `42P20`. | **Partial**. Window functions run as they do over the PostgreSQL dialect. The forms that dialect refuses, such as `GROUPS` frames and `DISTINCT` inside the call, are refused with `1235`. A window function in `WHERE` is `3593`. |
| Set operations | **Partial**. `UNION`, `INTERSECT` and `EXCEPT` run. `INTERSECT ALL` and `EXCEPT ALL` are refused with `0A000`, and so are `INTERSECT` and `EXCEPT` with `*` in either query, inside a subquery used as a value, or with `IN` or `EXISTS`. | **Partial**. The PostgreSQL dialect's limits apply, with `1235`. Text is compared exactly, so `SELECT 'a' INTERSECT SELECT 'A'` answers no rows, where MySQL's default collation ignores letter case. |
| Subqueries as a value | **Partial**. A subquery used as a single value fails when it returns more than one row or selects more than one column, under `P0001` and not PostgreSQL's `21000`. `IN`, `= ANY` and `EXISTS` accept any number of rows. `ALL` and `ANY` over a subquery with `GROUP BY`, `HAVING`, `LIMIT`, `OFFSET` or a set operation are refused. | **Partial**. A subquery used as a single value fails when it returns more than one row, with `1644`. The dialect shares its planner with the PostgreSQL dialect, so the same limits apply. |
| `LIKE` | **Partial**. Works in a `SELECT`, with `\%` and `\_` as literals, whether the pattern is written in the query, bound as a parameter or read from a column. `LIKE ... ESCAPE` is refused. | **Partial**. Works in a `SELECT`. `LIKE ... ESCAPE` is refused, and `e` and `é` are not equal in a comparison. |
| Columns in a join | **Differs**. In a table with declared columns, an undeclared column fails with `42703`. In a schemaless table, a column named without its table is read from the first table that has a value for it, so qualify the column to choose the table. | **Differs**. The dialect shares its planner with the PostgreSQL dialect, so a column named without its table in a join of schemaless tables is read from the first table that has a value for it. |
| `t.*` | **Partial**. `u.*` over a table or a derived table returns what `SELECT *` returns. In a join, `t.*` of a table with undeclared columns fails with `0A000`. Inside a function call, in `RETURNING`, in `CREATE VIEW` and beside another item in an `INSERT` query it is refused. | **Partial**. Behaves as it does over the PostgreSQL dialect, which shares the planner. |
| Generated rows | **Partial**. `generate_series(start, stop)` works in the select list. The form with a step, and `generate_series` or another set-returning function in `FROM`, are refused. | **Not documented** |
| Query nesting | **Refused**. A `FROM` clause of more than about 120 tables, joins nested in more than about 120 parentheses, or subqueries nested more than about 60 deep fail with `42601`. | **Refused**. A `FROM` clause that nests too deeply fails with `1064`. |
| Parameters | **Supported**. `$1`, `$2` and so on, in the extended query protocol. A parameter whose type the client leaves unspecified takes the type of what it is compared with or written to, and a text value that cannot be read as that type fails with `22P02`. | **Partial**. A `?` placeholder is accepted only in a prepared statement. |
| Prepared statements in SQL | **Supported**. `PREPARE`, `EXECUTE` and `DEALLOCATE` work on the connection. A name already in use fails with `42P05`, and an unknown name with `26000`. | **Supported**. `PREPARE ... FROM`, `EXECUTE ... USING` and `DEALLOCATE PREPARE` work, and so do user variables such as `SET @a = 1`. `EXECUTE` of an unknown name fails with `1243`. |
| Query modifiers | **Not applicable** | **Partial**. `SQL_CALC_FOUND_ROWS` is refused. `SQL_NO_CACHE`, `SQL_CACHE`, `SQL_SMALL_RESULT`, `SQL_BIG_RESULT`, `SQL_BUFFER_RESULT` and `HIGH_PRIORITY` are accepted and ignored. |

## Writes

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| `INSERT` | **Supported**. Multi-row `INSERT`, `ON CONFLICT` and `RETURNING` run. In `INSERT ... SELECT`, each item of the query is written to the column at its position, and the query can order, group, join or combine rows. A `*` in that query, or an `INSERT` with no column list, fails, so list the columns on both sides. | **Supported**. Multi-row `INSERT` and `INSERT ... ON DUPLICATE KEY UPDATE` run. An `INSERT` that gives an `AUTO_INCREMENT` primary key `NULL` is refused, where MySQL numbers the row. |
| `UPDATE` and `DELETE` | **Supported**. `UPDATE` and `DELETE` run, including `UPDATE ... FROM` and `DELETE ... USING`, which refuse `RETURNING *` with `0A000`. A `WITH` clause and subqueries can appear in a write. A subquery that reads a column of the row being written is refused with `0A000`. | **Supported**. `UPDATE` and `DELETE` run. |
| `LIKE` and `\|\|` in a write | **Refused**. 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. Functions such as `substring`, `ltrim`, `concat` and `extract` work. | **Refused**. `LIKE` is refused with `1235` in an `INSERT`, `UPDATE` or `DELETE`, including in its `WHERE` clause. Functions such as `SUBSTRING`, `LTRIM` and `CONCAT` work. |
| `TRUNCATE` | **Supported**. Takes a list of tables. A `serial` column keeps its count, as PostgreSQL's default `CONTINUE IDENTITY` does, and `TRUNCATE ... RESTART IDENTITY` starts it again from 1. | **Partial**. Starts the `AUTO_INCREMENT` numbering again from 1. It keeps the records that the table's permissions do not let the user delete, and still answers OK. A user who may delete records but not define a sequence is refused `TRUNCATE` of a table with an `AUTO_INCREMENT` column. |
| Constraint violations | **Supported**. A `UNIQUE` or primary key conflict is `23505`, a missing value is `23502` and a missing foreign key target is `23503`. | **Supported**. A `UNIQUE` or primary key conflict is `1062`, `NULL` for a required field is `1048` and an omitted required field is `1364`. |
| Value length | **Supported**. 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. | **Supported**. A value too long for a `VARCHAR(n)` column is refused with `1406`. |

## Schema and DDL

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| `CREATE TABLE` and `DROP TABLE` | **Supported**. A statement for several tables is answered once. If one part fails, nothing of the statement is kept, so `DROP TABLE a, missing` fails and keeps `a`. | **Supported**. A statement for several tables is answered with one OK packet, and if one part fails nothing of the statement is kept. |
| `ALTER TABLE` | **Partial**. `ALTER TABLE` with several actions runs, including `ADD FOREIGN KEY` and `DROP CONSTRAINT` with the names that `pg_constraint` lists. `RENAME COLUMN` answers with its command tag and changes nothing. | **Not supported** |
| Foreign keys | **Supported**. `REFERENCES` or `FOREIGN KEY` makes the column a record link to the other table. A write whose key names no record fails with `23503`. | **Not supported**. `CREATE TABLE` with a `FOREIGN KEY` constraint fails with `1105`. |
| Indexes and keys | **Supported**. `CREATE INDEX` defines indexes, including `hnsw` indexes for pgvector. An `int` primary key makes the record ID key an `i32`. | **Supported**. `UNIQUE` indexes and primary keys are enforced. `SHOW INDEX` lists them with MySQL's 15 columns, and `Cardinality` is `NULL`. |
| Views and other objects | **Partial**. `CREATE VIEW` over one table takes a projection, a filter and a column list, and the view stays up to date after every write to the table. A view over a join, or with `GROUP BY`, `DISTINCT`, `ORDER BY`, `LIMIT` or a set operation, is refused with `0A000`, and `CREATE MATERIALIZED VIEW` does not parse. `DROP VIEW` answers with the tag `DROP TABLE`. `CREATE EXTENSION vector` is accepted and defines nothing, and any other extension fails with `58P01`. `CREATE FUNCTION` does not parse. | **Not documented** |
| Schemas | **Partial**. The schema `public` is the selected database. `DROP SCHEMA public CASCADE` removes every table of the database in one all-or-nothing statement. `CREATE SCHEMA` answers with its command tag and changes nothing, and no other schema exists. | **Differs**. A database is named `ns/db`, and a statement that names a table in another database is refused. |
| User-defined types | **Supported**. `CREATE TYPE ... AS ENUM`, `CREATE DOMAIN` and `CREATE TYPE ... AS (...)` each define a named type. An enum column orders its values in declaration order, and a domain checks each `CHECK`. | **Supported**. An `ENUM('s','m','l')` column refuses any other value with `1366`, and orders its values in the order the column lists them. |
| Generated keys | **Supported**. A `serial` or `bigserial` column draws its numbers from a sequence. A table created again after `DROP TABLE` numbers its rows from 1. | **Supported**. An `AUTO_INCREMENT` column draws its numbers from a sequence and is `NOT NULL`. A table created again after `DROP TABLE` numbers its rows from 1. |
| DDL inside a transaction | **Supported**. DDL runs inside the open transaction, and `ROLLBACK` takes it back. | **Differs**. As in MySQL, `CREATE`, `ALTER`, `DROP`, `TRUNCATE` and `RENAME` first commit the open transaction and then run on their own, so `ROLLBACK` does not take back a `CREATE TABLE`. |
| Declared columns | **Supported**. A table declared with `CREATE TABLE` or `DEFINE TABLE ... SCHEMAFULL` is checked as PostgreSQL checks a table. `SELECT *` lists `id` first and then the other columns by name, not in declaration order. | **Supported**. `CREATE TABLE` declares the columns. Columns and indexes are listed by name, not in the order they were defined. |

## Transactions

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Interactive transactions | **Supported**. `BEGIN`, `COMMIT` and `ROLLBACK`, with PostgreSQL's ready-for-query status bytes. After an error in a block, commands receive `25P02` until `COMMIT` or `ROLLBACK`. | **Supported**. `START TRANSACTION` or `BEGIN`, `COMMIT` and `ROLLBACK`. `SET autocommit = 0` makes each statement run in a transaction until `COMMIT`. |
| Autocommit | **Differs**. Each top-level statement outside `BEGIN ... COMMIT` commits on its own, and in a simple query of several statements outside a transaction block, a failing statement does not stop the statements after it. Wrap statements in a transaction for all-or-nothing behaviour. | **Supported**. Each statement commits on its own until `SET autocommit = 0`. |
| Savepoints | **Supported**. `SAVEPOINT`, `RELEASE SAVEPOINT` and `ROLLBACK TO SAVEPOINT` work in a transaction block, and `SET LOCAL` settings are undone on rollback. | **Supported**. `SAVEPOINT` works in a transaction. |
| Isolation levels | **Partial**. `SERIALIZABLE` opens a serializable transaction on the storage engines that offer one, and fails with `0A000` on the others. `REPEATABLE READ` and a plain `BEGIN` run at snapshot isolation. `READ COMMITTED` and `READ UNCOMMITTED` are refused with `0A000`. `BEGIN READ ONLY` is applied. | **Not documented** |
| Conflicts | **Supported**. A transaction that conflicts with another fails with `40001`. Retry it. | **Supported**. A transaction that conflicts with another fails with `1213`. Retry it. |

## Types and values

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Sized numbers | **Supported**. `smallint`, `integer`, `bigint`, `real` and `double precision` define fields of the types `i16`, `i32`, `int`, `f32` and `float`. The catalog lists an `int` column as `integer` (`int4`). | **Supported**. `TINYINT` is `i8`, `SMALLINT` is `i16`, `MEDIUMINT` and `INT` are `i32`, `BIGINT` is `int`, `INT UNSIGNED` is `u32`, `FLOAT` is `f32` and `DOUBLE` is `float`. |
| Decimals | **Supported**. A `numeric` column with a precision above 28, or with none, defines a `bigdecimal` field. | **Supported**. A `DECIMAL` column with a precision above 28 defines a `bigdecimal` field. `CAST(x AS UNSIGNED)` wraps a negative integer to the top of the unsigned range, as in MySQL. |
| Out-of-range and mistyped values | **Differs**. A value out of range, such as `40000` for a `smallint`, is refused with `42804`. SurrealDB refuses text for a number field, even `'5'`, where PostgreSQL accepts it or reports `22P02`. | **Differs**. A value out of range, such as `300` for a `TINYINT` or `-1` for an `INT UNSIGNED`, is refused with `1366`. SurrealDB refuses some values that MySQL converts, such as `'5'` or `true` for an `INT` column. |
| Typed literals | **Partial**. Write a typed value as a cast, such as `'2026-01-01'::date`. The `DATE '2026-01-01'` form does not parse, and a plain string is not converted to a `date` column. A parameter is converted to the type of a declared `date`, `timestamp` or `timestamptz` column that it is written to. | **Partial**. `DATE_ADD(d, INTERVAL 1 DAY)` and the other `INTERVAL` forms run. Compound interval units such as `DAY_HOUR` are refused with `1235`. A parameter is converted to the type of a declared `DATE`, `DATETIME` or `TIMESTAMP` column that it is written to. |
| Result column types | **Differs**. A result column is announced as a type that all its values have. An integer constant is an `integer` when it fits one and a `bigint` otherwise, as in PostgreSQL, and a value that does not fit the announced type fails with `22003`. `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. An arithmetic expression over two fields with no declared type is announced as `bigint`. | **Differs**. A column without an alias is named by its expression as written, as in MySQL. In a batch of several statements, only the first statement keeps its column names, and unnamed or repeated columns of later statements are named `?column?1`, `?column?2` and so on. |
| Arrays | **Partial**. `@>`, `<@` and `&&` compare arrays as sets, with a column, a parameter, a cast or a scalar subquery as an operand. An operand written as a text literal, such as `'{ORM}'`, the `ARRAY[...]` constructor and `@>` over a JSON column are refused with `0A000`. | **Not applicable** |
| Vectors | **Supported**. `vector(n)` columns, distance operators and `hnsw` indexes, as described under [pgvector](#pgvector). | **Not applicable** |

## Functions

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Built-in functions | **Partial**. The implemented functions are tested against PostgreSQL 18, including the keyword forms `extract(year FROM d)`, `position('b' IN s)`, `substring(s FROM 2 FOR 3)` and `overlay`. A function that does not exist fails with `42883`. | **Partial**. The implemented functions are tested against MySQL 8.4, including `EXTRACT(YEAR FROM d)`, `POSITION('b' IN s)`, `SUBSTRING(s FROM 2 FOR 3)` and `INTERVAL` arithmetic. `LENGTH` counts bytes. `CAST(x AS UNSIGNED)` of a stored text that is not a number, such as `'12abc'`, fails. A function that does not exist fails with `1305`. |
| SurrealQL functions | **Not supported**. The PostgreSQL dialect calls SQL functions only, so `http::get` and `fn::` functions are unavailable. Switch the dialect to SurrealQL to call them. | **Not supported**. The MySQL dialect calls SQL functions only. Switch the dialect to SurrealQL to call SurrealQL functions. |
| Date arithmetic | **Supported**. Date functions such as `date_trunc` run. An unknown unit fails with `22023`. | **Partial**. `TIMESTAMPDIFF` with the unit `MONTH`, and compound interval units such as `DAY_HOUR`, are refused with `1235`. |
| Restricted functions | **Differs**. A `LIKE` pattern that is not written in the query uses `string::replace` and `string::concat`, so an instance that [restricts functions](/docs/learn/security/authorization/capabilities.md#functions) must allow those two. | **Differs**. An instance that denies built-in functions refuses the `SHOW` and `DESCRIBE` statements. |

## Introspection

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Catalog relations | **Supported**. `information_schema.tables`, `information_schema.columns`, `pg_class`, `pg_attribute`, `pg_constraint`, `pg_index` and the other catalog relations that drivers and ORMs read list the tables of the database, in the schema `public`. A table without declared columns lists only `id`, and `pg_matviews` has no rows. | **Differs**. `information_schema` lists the tables and declared columns in PostgreSQL's form: the tables are in the schema `public`, `data_type` holds PostgreSQL type names and `column_type` is `NULL`, so `WHERE table_schema = DATABASE()` finds none. |
| `SHOW` and `DESCRIBE` | **Partial**. `SHOW` and `RESET` are supported. | **Supported**. `SHOW TABLES`, `COLUMNS`, `FIELDS`, `INDEX`, `KEYS`, `DATABASES`, `VARIABLES`, `ENGINES`, `COLLATION` and `CHARACTER SET`, and `DESCRIBE`, answer with the column names, order and types of MySQL 8.4. `SHOW TABLE STATUS` answers with MySQL's 18 columns. `SHOW STATUS`, `PROCESSLIST`, `WARNINGS`, `ERRORS` and `TRIGGERS` answer with MySQL's columns and no rows. |
| `SHOW CREATE TABLE` | **Not documented** | **Differs**. Returns the SurrealQL definition, not MySQL DDL. |
| System variables | **Partial**. `SET`, `SET LOCAL` and `SHOW` work. `SET hnsw.ef_search` is accepted and has no effect. | **Partial**. `@@name`, `@@session.name`, `@@local.name` and `@@global.name` read the same fixed value, and a `SET` does not change it. A name that MySQL 8.4 or 8.0.35 does not have fails with `1193`. Variables that describe the host read `NULL`. |

## Bulk load and migration

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| Bulk load and export | **Partial**. `COPY ... FROM STDIN` and `COPY ... TO STDOUT` run in the text and CSV formats. Name the columns in `COPY ... FROM STDIN`. `COPY ... FROM` into a vector column does not convert text to a vector. | **Not supported**. `LOAD DATA` is not supported yet. |
| Import from another database | **Supported**. See [Migrating from PostgreSQL](/docs/build/migrating/from-other-databases/from-postgresql.md). | **Supported**. See [Migrating from MySQL](/docs/build/migrating/from-other-databases/from-mysql.md). |

## pgvector

The PostgreSQL dialect accepts the column type, the distance operators and the `hnsw` indexes of the [pgvector](https://github.com/pgvector/pgvector) extension. The MySQL dialect has no vector type. The [PostgreSQL protocol page](/docs/reference/rest-api/postgres-protocol.md#pgvector) describes each part in detail.

| Feature | Verdict and detail |
| --- | --- |
| `vector(n)` columns | **Supported**. A field of type `array<float, n>`, up to 16,000 dimensions. A column without `(n)` is refused with `0A000`. Drivers see the `vector` type in the text and binary formats, so the pgvector packages for psycopg, asyncpg and node-postgres find it. |
| Distances | **Supported**. `<->` is `l2_distance`, `<=>` is `cosine_distance`, `<#>` is `vector_negative_inner_product` and `<+>` is `l1_distance`. `inner_product` returns the inner product itself. Vectors of different dimensions fail with `22000`. |
| `hnsw` indexes | **Supported**. `CREATE INDEX ... USING hnsw` with `vector_l2_ops`, `vector_cosine_ops`, `vector_ip_ops` or `vector_l1_ops`. `WITH (m, ef_construction)` takes pgvector's defaults and limits, and an index covers at most 2,000 dimensions. |
| Nearest-neighbour search | **Supported**. `ORDER BY column <op> value LIMIT k` over one table is answered from the index when the column has exactly one index that ranks by the same distance, and by an exact search otherwise. |
| `halfvec`, `sparsevec`, `bit`, `ivfflat` | **Not supported** |
| Other `hnsw` forms | **Refused**. Partial, unique, multi-column and expression indexes are refused with `0A000`. |
| `hnsw.ef_search` | **Differs**. `SET hnsw.ef_search` is accepted, and the search uses an `ef` of 40, or `k` when it is larger. |
| Bound limit | **Differs**. `LIMIT $1` sorts every row. Write `k` in the query to use the index. |
| Distance in a write | **Refused**. A distance operator or function in an `INSERT`, `UPDATE` or `DELETE` is refused with `0A000`. |
| Vector utilities | **Not supported**. `DROP EXTENSION`, `vector_dims`, `vector_norm`, `l2_normalize`, vector arithmetic and `avg` over a vector column. |

## Refusal codes

Features that a dialect does not run are refused with one code, so a client can tell a refusal from a syntax error:

| Listener | Code | Reported when |
| --- | --- | --- |
| PostgreSQL | `0A000` (`feature_not_supported`) | The statement uses a feature that SurrealDB does not support, such as a `GROUPS` window frame, a recursive common table expression or `INTERSECT ALL`. |
| PostgreSQL | `42601` (`syntax_error`) | The statement does not parse, such as `CREATE FUNCTION`, or exceeds a nesting limit. |
| MySQL | `1235` | The statement uses a feature that SurrealDB does not support, such as `DISTINCT` inside a window function or a recursive common table expression. |
| MySQL | `1064` | The statement does not parse, such as a `LATERAL` subquery, or a `FROM` clause nests too deeply. |

The full list of codes is in [Error codes](/docs/reference/rest-api/postgres-protocol.md#error-codes) on the Postgres page and [Error codes](/docs/reference/rest-api/mysql-protocol.md#error-codes) on the MySQL page.

## Limits and capabilities

- The `postgres` and `mysql` routes are [capabilities](/docs/learn/security/authorization/capabilities.md). `--deny-http postgres` and `--deny-http mysql` refuse every connection to the matching listener, and `SET dialect = 'gql'` needs the `gql` route.
- `--deny-arbitrary-query` applies to both listeners, as it does to the other query surfaces.
- The MySQL listener accepts at most 1,024 connections at a time and refuses further connections with error `1040`. A MySQL packet is limited to 64 MiB, and a larger packet is refused with error `1153`.

## Related pages

- [Postgres wire protocol](/docs/reference/rest-api/postgres-protocol.md): the PostgreSQL listener and dialect
- [MySQL wire protocol](/docs/reference/rest-api/mysql-protocol.md): the MySQL listener and dialect
- [Migrating from PostgreSQL](/docs/build/migrating/from-other-databases/from-postgresql.md) and [Migrating from MySQL](/docs/build/migrating/from-other-databases/from-mysql.md): move data and map types
- [Capabilities](/docs/learn/security/authorization/capabilities.md): restrict routes, functions and arbitrary queries