Available 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 and MySQL wire protocol 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. | 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 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. | Supported See Migrating from MySQL. |
pgvector
The PostgreSQL dialect accepts the column type, the distance operators and the hnsw indexes of the pgvector extension. The MySQL dialect has no vector type. The PostgreSQL protocol page 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 on the Postgres page and Error codes on the MySQL page.
Limits and capabilities
The
postgresandmysqlroutes are capabilities.--deny-http postgresand--deny-http mysqlrefuse every connection to the matching listener, andSET dialect = 'gql'needs thegqlroute.--deny-arbitrary-queryapplies 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 error1153.
Related pages
Postgres wire protocol: the PostgreSQL listener and dialect
MySQL wire protocol: the MySQL listener and dialect
Migrating from PostgreSQL and Migrating from MySQL: move data and map types
Capabilities: restrict routes, functions and arbitrary queries