From SurrealDB 4.0, a connection runs the PostgreSQL dialect of SQL, and can switch to ISO GQL. SurrealQL is not available over this protocol from 4.0: send it over HTTP, RPC 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 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:
psqlas 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) | |
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 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):
surreal db start --user root --pass secret \
--postgres-bind 127.0.0.1:5432 \
memoryEnvironment variable equivalent:
export SURREAL_POSTGRES_BIND=127.0.0.1:5432
surreal db start --user root --pass secret memoryConnect with psql:
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 (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.
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
A client can also send a token that SurrealDB issued, such as the token a record user 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, 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.
Try it with psql
Start a local instance and connect (see Start the listener):
psql "host=127.0.0.1 port=5432 user=root password=secret dbname=main/main"Create a table and add some rows:
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:
-- 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_isreadyreports the listener as accepting connections, over plaintext and TLS.pgbenchruns custom scripts given with-f.pgbench -iruns every initialisation step except the data load, which fails becausepgbenchsendsCOPY ... FROM STDINwithout a column list. Leave that step out withpgbench -i -I dtpv. For these steps,CREATE TABLE ... WITH (...)storage parameters are accepted and discarded,VACUUMandANALYZEanswer with their command tags and do nothing, andTRUNCATEtakes a list of tables.pg_dumpdoes 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 readpg_matviewsat startup, such as TypeORM, can run those statements, as the 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. 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
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 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.
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:1is sent as1. Theidcolumn, aWHERE id = 1condition, joins on keys, and a write to a field defined asrecord<T>all use the key.SQL functions only. Queries can call the PostgreSQL functions that SurrealDB implements, but no SurrealQL function, such as
http::getor afn::function. The implemented functions are tested against the answers that PostgreSQL 18 gives for the same arguments, and include the keyword forms such asextract(year FROM d),position('b' IN s),substring(s FROM 2 FOR 3)andoverlay. An error that a function raises is reported with PostgreSQL's SQLSTATE, as listed under Error codes.Schema inspection.
information_schema.tables,information_schema.columns,pg_class,pg_attribute,pg_constraint,pg_indexand the other catalog relations that drivers, ORMs and schema browsers read list the tables of the database, in the schemapublic. A column is listed when the table declares it, so a table without declared columns lists onlyid.pg_matviewshas no rows, because SurrealDB has no materialised views.The schema
public. The schemapublicis the selected database, andpublic.tis the tablet.DROP SCHEMA public CASCADEremoves every table of the database in one all-or-nothing statement, views before the tables they read, together with the sequences of theirserialcolumns, and needs the role thatDROP TABLEof each table needs. WithoutCASCADE, it fails with0A000when the database holds a table, where PostgreSQL reports2BP01. No other schema exists, soDROP SCHEMAof another name fails with42P01unless it hasIF EXISTS, andpg_catalog,information_schemaandpg_toastare refused with0A000.Sized number columns.
smallint,integer,bigint,realanddouble precisioncolumns define fields of the sized number typesi16,i32,int,f32andfloat, so anintcolumn is ani32field and the catalog lists it asinteger(int4). A value out of range for the column, such as40000for asmallint, is refused with42804. Anintprimary key makes the record ID key ani32as well.Declared column types.
CREATE TABLErecords the type of each column as the field'sSQL TYPE, and every write is checked against it. A value too long for avarchar(n)column is refused with22001, and anumeric(p,s)column rounds a value to its scale. Anumericcolumn with a precision above 28, or with none, defines abigdecimalfield.CREATE TYPE ... AS ENUM,CREATE DOMAINandCREATE TYPE ... AS (...)each define a named type withDEFINE 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 oneASSERTfor eachCHECK; and a composite type becomes an object type.ALTER DOMAINredefines the type, andDROP DOMAINandDROP TYPEremove it.Foreign keys. A
REFERENCESclause or aFOREIGN KEYconstraint, inCREATE TABLEor inALTER TABLE ... ADD FOREIGN KEY, makes the column a record link to the other table. A write whose key names no record in that table fails with23503.ALTER TABLE ... DROP CONSTRAINTtakes the names thatpg_constraintlists, such asnote_person_id_fkey.Generated keys. A
serialorbigserialcolumn draws its numbers from a sequence. A table created again afterDROP TABLEnumbers its rows from 1.TRUNCATEkeeps the count, as PostgreSQL's defaultCONTINUE IDENTITYdoes, andTRUNCATE ... RESTART IDENTITYstarts it again from 1.RESTART IDENTITYdefines 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_valueandnth_valuerun over a window, and so dosum,avg,count,minandmax. A window takesPARTITION BY,ORDER BYwithNULLS FIRSTorNULLS LAST, a namedWINDOWclause, aROWSframe, and aRANGEframe bounded byUNBOUNDEDorCURRENT ROW. A window can read aGROUP BYresult, as inrank() 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)andDEALLOCATE [PREPARE] name | ALLwork on the connection. A name already in use fails with42P05, and an unknown name with26000.Bulk load and export.
COPY ... FROM STDINandCOPY ... TO STDOUTmove rows in the text and CSV formats. Name the columns inCOPY ... FROM STDIN, for exampleCOPY 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 adatecolumn, and theDATE '2026-01-01'form does not parse.Declared columns. A table that declares its columns, with
CREATE TABLEorDEFINE TABLE ... SCHEMAFULL, is checked as PostgreSQL checks a table. A column that the table does not declare fails with42703,SELECT *returns the declared columns, withNULLfor an optional column that a record does not hold, and drivers are told the declared types.*listsidfirst 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 asNULL, 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
NULLinstead 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 inb.w, to choose the table.idwithout a table is always the first table'sid. In aFROMclause of more than 64 tables, a column named without its table fails with0A000and must be qualified.t.*.SELECT u.* FROM users ureturns whatSELECT *returns, over a table or a derived table. In a join,t.*of a table whose columns are not declared fails with0A000.Table aliases. A table given an alias is reachable only by the alias, as in PostgreSQL, so
SELECT users.id FROM users ufails with42P01.LIKEpatterns. 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 thestring::replaceandstring::concatfunctions, so an instance that restricts functions must allow those two. A pattern too long to compile fails withLIKE 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. AddORDER BY ... LIMIT 1or a narrowerWHEREto choose one row. The error is reported under SQLSTATEP0001, not PostgreSQL's21000.IN,= ANYandEXISTSaccept 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 TABLEof several tables andALTER TABLEwith several actions are each answered once. If one part fails, nothing of the statement is kept, soDROP TABLE a, missingfails and keepsa.Nesting limits. A
FROMclause 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 with42601parser 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 JOINare refused with0A000(feature_not_supported). Non-recursiveWITHqueries work.In a window function,
GROUPSframes,EXCLUDEother thanEXCLUDE NO OTHERS,RANGEframes with a numeric offset, frame offsets that are not literals,lagandleadoffsets andnth_valuepositions that read the row, andDISTINCTinside the call are refused with0A000. APARTITION BYorORDER BYkey whose value can change when it is evaluated again, such asrandom() < 0.5, makes the query fail.INTERSECT ALLandEXCEPT ALLare refused with0A000. So areINTERSECTandEXCEPTwith*in either query, with a computed column that has no alias inside a derived table, inside a subquery used as a value or withINorEXISTS, and in a chain of three or more queries.An
ORDER BYinside an aggregate call, as instring_agg(name, ',' ORDER BY id), does not parse, and fails with42601.CREATE FUNCTIONdoes not parse, and fails with42601(syntax_error).A
LATERALsubquery runs in aCROSS JOINor an inner join when it is aSELECTof expressions with no other clause, as inCROSS JOIN LATERAL (SELECT u.id + 1 AS n) d. ALEFT JOIN LATERAL, and a lateral subquery with aFROMclause, aWHEREor an aggregate, are refused with0A000.A write that uses a clause the dialect cannot run, such as
UPDATE ... FROM,DELETE ... USING, aWITHclause, a subquery in its condition or values, or anINSERT ... SELECTthat reads from a join or a subquery, is refused with0A000before 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 inINSERT INTO users (id, name) SELECT id + 10 AS id, title AS name FROM posts.CREATE VIEWover 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 VIEWdoes not parse.The array operators
@>,<@and&&, and theARRAY[...]constructor, are refused with0A000.The
||andLIKEoperators are refused with0A000in anINSERT,UPDATEorDELETE, including in itsWHEREclause, soDELETE FROM t WHERE name LIKE 'a%'fails before it deletes anything. They work in aSELECT. Functions, such assubstring,ltrim,concatandextract, work in both, apart from the pgvector distance functions.generate_seriestakes two arguments and works in the select list, as inSELECT generate_series(1, 5). The form with a step, andgenerate_seriesor another set-returning function inFROM, are refused.A column in the select list or in
HAVINGthat 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 inWHEREis not refused either.LIKE ... ESCAPEis refused. A trailing backslash in a pattern matches a backslash.ALLandANYcomparisons over a subquery that hasGROUP BY,HAVING,LIMITorOFFSET, or is a set operation, are refused.UNIONinside 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.*)), inRETURNING, inCREATE VIEW, and beside another item in the query of anINSERTis refused.CREATE SCHEMAandALTER TABLE ... RENAME COLUMNanswer with their command tag but change nothing.DROP VIEWanswers with the tagDROP TABLE.An arithmetic expression over two fields with no declared type, such as
a + b, is announced asbigint, so a result that is a float fails to encode.
pgvector
The PostgreSQL dialect accepts the column type, distance operators and hnsw indexes of the 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, so SurrealQL queries can read and search the same data.
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,sparsevecandbittypes, binary quantisation and iterative index scans.ivfflatindexes, andhnswindexes that are partial, unique, over more than one column or over an expression. Each is refused with0A000.hnsw.ef_search.SET hnsw.ef_searchis accepted, but the index search always uses anefof 40, orkwhen it is larger.A bound limit, such as
LIMIT $1.kmust 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,UPDATEorDELETE, which is refused with0A000. Use them in aSELECT.COPY ... FROMinto 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
SELECTin that request, which searches exactly.DROP EXTENSION,vector_dims,vector_norm,l2_normalize, arithmetic on vectors andavgover 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 when you opt in - the same engine as POST /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
psqlwithSET 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 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:
surreal db start --user root --pass secret \
--postgres-bind 127.0.0.1:5432 \
memorySelect GQL for the session:
-- 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.
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:
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/RETURNoutput) become one column per object key, with types inferred from the values and promoted across rows (for exampleintandfloatwiden tofloat8/numeric).Scalars and non-object arrays become a single
valuecolumn.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, results follow PostgreSQL more closely:
A command that returns no rows, such as
CREATE TABLE,DROP TABLE,TRUNCATEor anINSERTwithoutRETURNING, 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
SELECTkeeps its own value, including columns that share a name, as inSELECT a.k, b.k FROM a JOIN b ON a.k = b.k, and columns with no name, as inSELECT 1, 'a'.A result column is announced as a type that all its values have.
floorandceiltake the type of their argument.CASE,COALESCE,GREATESTandLEASTtake the widest number type among their alternatives, in PostgreSQL's order (smallint,integer,bigint,numeric,real,double precision), ortextwhen the alternatives are not all numbers. ANULLalternative does not change the type, and a field with no declared type counts astext. The type can differ from PostgreSQL's:floor(1.5)is a float here andnumericin PostgreSQL, and aCASEover 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
jsonbcolumn when SurrealDB cannot know the columns before the statement runs. A preparedSELECTover 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 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.
Capabilities and security
Connections are gated like other query surfaces:
RouteTarget::Postgres- controlled via--allow-http/--deny-httpwith the route namepostgres(the capability helper is shared with HTTP route names).Arbitrary query - subject to
--allow-arbitrary-query/--deny-arbitrary-queryforguest,record, andsystemusers.GQL - enabled by default from 3.3.0, so no experimental capability is required.
Authentication is described in 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 | HTTP | SurrealQL | curl, scripts |
POST /gql | HTTP | ISO GQL | curl, HTTP clients |
| RPC | HTTP / WebSocket | SurrealQL (+ RPC methods, including pgsql from 4.0) | Official SDKs |
| Postgres wire | TCP (Postgres v3) | PostgreSQL SQL (SurrealQL before 4.0) | psql, JDBC, tokio-postgres, … |
| MySQL wire (from 4.0) | TCP (MySQL protocol) | MySQL SQL | mysql, MySQL drivers |
Related pages
SurrealQL reference: statements, types, and clauses
MySQL wire protocol: the equivalent listener for MySQL clients, which can also select the PostgreSQL dialect
PostgreSQL and MySQL compatibility matrix: the two dialects compared feature by feature
GQL overview: ISO GQL concepts and wire surfaces
GQL via HTTP: enable GQL and run queries with
curlDEFINE USER: SCRAM verifier material for Postgres clientsCapabilities: lock down arbitrary queries and routes
surreal db start: CLI reference including--postgres-bindMySQL wire protocol: connect with MySQL clients and drivers