PostgreSQL Compatibility

immudb implements the PostgreSQL wire protocol , providing broad compatibility with PostgreSQL clients, ORMs, and tools. Connect with psql, pgAdmin, DBeaver, JDBC, SQLAlchemy, Django, GORM, ActiveRecord, and more.

immudb reports itself as PostgreSQL 14.0 and supports:

• COPY FROM stdin for bulk data import via psql -f dump.sql
• pg_catalog query interception for tool compatibility (pgAdmin, DBeaver)
• PostgreSQL type aliases: BIGINT, SERIAL, NUMERIC, DECIMAL, BYTEA, JSONB, TIMESTAMPTZ, TEXT, etc.
• LIKE with standard SQL wildcards (% and _, not regex)
• 75+ built-in functions compatible with PostgreSQL
• Automatic type translation from PostgreSQL DDL to immudb types
• immudb-only extensions: see SQL Diff Query for transaction-range table diffs against the immutable transaction log

Start immudb with the pgsql-server option enabled:

1./immudb --pgsql-server --pgsql-server-port 5433

Or via environment variable: IMMUDB_PGSQL_SERVER=true.

SSL is supported if you configure immudb with a certificate. Without SSL, clients that send an SSL probe will gracefully fall back to plaintext.

Importing PostgreSQL databases#

You can import standard pg_dump output directly:

1PGPASSWORD=immudb psql -h localhost -p 5433 -U immudb -d defaultdb -f mydatabase.sql

immudb automatically translates PostgreSQL types, handles COPY FROM stdin data, and silently skips unsupported DDL (CREATE FUNCTION, CREATE TRIGGER, GRANT, etc.).

ORM and Application Compatibility#

The PostgreSQL wire protocol and SQL engine have been hardened against the corner cases real-world ORMs and applications hit. Verified workloads include Gitea 1.25.5 (full signup → repo creation → git push → issue lifecycle), Ruby on Rails 7 / ActiveRecord (Maybe Finance dashboard), Django, GORM, XORM, golang-migrate, SQLAlchemy, and the lib/pq and pgx Go drivers.

System catalog and introspection emulation#

ORMs probe these on every connection; immudb returns realistic results for:

• pg_catalog: pg_class, pg_attribute, pg_index, pg_indexes, pg_constraint, pg_type, pg_namespace, pg_roles, pg_settings, pg_description, pg_tables
• information_schema: tables, columns, schemata, key_column_usage
• Function emulation: current_database(), current_schema(), current_user, format_type(), pg_encoding_to_char(), pg_get_indexdef(), regclass and regtype casts
• An XORM column-introspection short-circuit so schema syncs don’t issue thousands of slow catalog reads

Reserved-keyword identifier round-trip#

ORMs that quote "index", "key", "value", "user", "order", "check", etc. now Just Work. Quoted identifiers map to a _<word> column on disk, and the wire layer reverse-renames them on the way out so client struct mappers see the original name.

Parameter-bind protocol fixes#

Correct text- and binary-format handling for every Postgres type immudb supports:

• BYTEA in canonical PG hex format (\x<hex>) for both bind and result paths
• BOOLEAN accepts t/f, true/false, 1/0
• TIMESTAMP accepts the Rails-style YYYY-MM-DD HH:MM:SS.ffffff text form
• FLOAT8, INT8, JSONB, TIMESTAMPTZ OIDs in RowDescription for ORM type inference
• NULL binds across all types
• Implicit VARCHAR → BOOLEAN coercion for ORMs that never declare a parameter type
• Bind type inference recurses into subquery expressions (IN (SELECT …), scalar subqueries, EXISTS, CASE WHEN, EXTRACT, ORDER BY with bind params), so lib/pq’s ParameterDescription matches what the client is about to send

SQL grammar additions and fixes for ORM-emitted shapes#

• Unqualified column references inside JOIN/WHERE resolve across all FROM-scope tables (XORM emits JOIN issue_assignees ON assignee_id = user.id without table qualifier)
• Scalar subqueries usable in WHERE, SELECT projection, and ORDER BY
• COUNT(DISTINCT col), COUNT(1) (rewritten to COUNT(*)), STRING_AGG(col, sep), SUM(CASE WHEN col = ? THEN 1 ELSE 0 END) (rewritten when the shape matches)
• Alias names that match aggregate keywords (SELECT id AS sum FROM …)
• LIKE and ILIKE with standard SQL wildcards (%, _)
• Hash aggregate path for GROUP BY without sorted input; projection pushdown that skips decoding columns the query doesn’t reference; secondary index used for WHERE-only SELECT

DML correctness#

Semantics now match Postgres for the patterns ORMs rely on most:

• INSERT … ON CONFLICT DO UPDATE SET col = expr reads the EXISTING row’s values when reducing expr (so per-group counters like XORM’s max_index = max_index + 1 actually increment)
• RETURNING capture is reset on every prepared-statement re-execution (so reused INSERTs over Bind/Execute don’t return stale rows from earlier executions)
• INSERT INTO schema_migrations is automatically idempotent (ON CONFLICT DO NOTHING) so Rails and golang-migrate can re-run schema syncs safely
• Multi-statement transactions, SAVEPOINT / ROLLBACK TO SAVEPOINT, and explicit BEGIN / COMMIT / ROLLBACK track transaction status correctly so lib/pq and pgx accept the next query

Operability#

Benign client disconnects (Rails connection-pool churn, Gitea eventsource long-poll cancels) are demoted from [E] to debug log level; a per-session SQL parse cache and an in-memory catalog cache reduce per-query overhead under ORM workloads.

1 #include <libpq-fe.h>

1require 'pg'

1$ javac -cp .:./postgresql-42.2.20.jar MyProgram.java

To connect to the database:#

1psql "host=localhost dbname=defaultdb user=immudb password=immudb sslmode=disable"
2psql (13.2, server 0.0.0)
3Type "help" for help.

1PGconn *conn = PQconnectdb("host=localhost user=immudb password=immudb dbname=defaultdb sslmode=disable");
2
3if (PQstatus(conn) == CONNECTION_BAD) {
4  fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
5  PQfinish(conn);
6  exit(1);
7}

1conn = PG::Connection.open("sslmode=allow dbname=defaultdb user=immudb password=immudb host=127.0.0.1 port=5432")

1Connection conn = 
2  DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/defaultdb?sslmode=allow&preferQueryMode=simple",
3    "immudb", "immudb");
4System.out.println("Opened database successfully");

1<?php
2$dbconn = pg_connect("host=localhost port=5432 sslmode=require user=immudb dbname=defaultdb password=immudb");
3//...
4pg_close($dbconn);
5?>

Execute statements:#

1defaultdb=> CREATE TABLE Orders(id INTEGER, amount INTEGER, title VARCHAR, PRIMARY KEY id);
2SELECT 1
3defaultdb=> UPSERT INTO Orders (id, amount, title) VALUES (1, 200, 'title1');
4SELECT 1

 1PGresult *res = PQexec(conn, "CREATE TABLE Orders (id INTEGER, amount INTEGER, title VARCHAR, PRIMARY KEY id)");
 2if (PQresultStatus(res) != PGRES_COMMAND_OK) {
 3  do_exit(conn, res);
 4}
 5PQclear(res);
 6
 7res = PQexec(conn, "UPSERT INTO Orders (id, amount, title) VALUES (1, 200, 'title 1')");
 8if (PQresultStatus(res) != PGRES_COMMAND_OK) {
 9  do_exit(conn, res);
10}
11PQclear(res);

1conn.exec( "CREATE TABLE Orders (id INTEGER, amount INTEGER, title VARCHAR, PRIMARY KEY id)" )
2conn.exec( "UPSERT INTO Orders (id, amount, title) VALUES (1, 200, 'title 1')" )
3conn.exec( "UPSERT INTO Orders (id, amount, title) VALUES (2, 400, 'title 2')" )

1Statement stmt = conn.createStatement();
2
3stmt.executeUpdate("CREATE TABLE people(id INTEGER, name VARCHAR, salary INTEGER, PRIMARY KEY id);");
4
5stmt.executeUpdate("INSERT INTO people(id, name, salary) VALUES (1, 'Joe', 20000);");
6stmt.executeUpdate("INSERT INTO people(id, name, salary) VALUES (2, 'Bob', 30000);");

1$stmt = 'CREATE TABLE people(id INTEGER, name VARCHAR, salary INTEGER, PRIMARY KEY id);';
2$result = pg_query($stmt) or die('Error message: ' . pg_last_error());
3$stmt = 'INSERT INTO people(id, name, salary) VALUES (1, 'Joe', 20000);';
4$result = pg_query($stmt) or die('Error message: ' . pg_last_error());
5$stmt = 'INSERT INTO people(id, name, salary) VALUES (2, 'Bob', 30000);';

Query and iterate over results:#

1defaultdb=> SELECT id, amount, title FROM Orders;
2 (defaultdb.Orders.id) | (defaultdb.Orders.amount) | (defaultdb.Orders.title)
3-----------------------+---------------------------+--------------------------
4                     1 |                       200 | "title1"
5(1 row)

 1res = PQexec(conn, "SELECT id, amount, title FROM Orders");
 2if (PQresultStatus(res) != PGRES_TUPLES_OK) {
 3  printf("No data retrieved\n");
 4  PQclear(res);
 5  do_exit(conn, res);
 6}
 7
 8int rows = PQntuples(res);
 9for(int i=0; i<rows; i++) {
10  printf("%s %s %s\n", PQgetvalue(res, i, 0),
11  PQgetvalue(res, i, 1), PQgetvalue(res, i, 2));
12}
13PQclear(res);
14PQfinish(conn);

1conn.exec( "SELECT id, amount, title FROM Orders" ) do |result|
2  result.each do |row|
3    puts row.inspect
4  end
5end

1ResultSet rs = stmt.executeQuery("SELECT * FROM people");
2
3while(rs.next()){
4    System.out.print("ID: " + rs.getInt("(defaultdb.people.id)"));
5    System.out.print(", Name: " + rs.getString("(defaultdb.people.name)"));
6    System.out.print(", Salary: " + rs.getInt("(defaultdb.people.salary)"));
7    System.out.println();
8}

1$query = 'SELECT * FROM people';
2$result = pg_query($query) or die('Error message: ' . pg_last_error());
3while ($row = pg_fetch_row($result)) {
4  var_dump($row);  
5}