OriginChainDB / Guides

PostgreSQL compatibility

Connect through the PostgreSQL endpoint, understand transaction behavior and handle the differences that matter to an application.

Connect a driver or ORM

Use the PostgreSQL connection details shown in the instance’s Connect dialog and follow the client connection guide. PostgreSQL protocol support does not imply full PostgreSQL behavior or support for every driver and ORM feature. Check the statements your application sends, including setup and schema-management commands.

Review baseline: the engine artifact promoted on 22 October 2026. Individual instances may still run an older build. Capture /v1/capabilities and test the exact connection mode used by your application before an upgrade.

Transaction behavior

• Use BEGIN, COMMIT and ROLLBACK for supported transactional writes. Statements inside a transaction read that transaction’s earlier writes.
• Conflicting concurrent writes can require retrying the transaction. A transaction cannot commit into a table another session has truncated or replaced.
• On PostgreSQL connections, READ ONLY refuses write statements. COMMIT AND CHAIN and ROLLBACK AND CHAIN open the next transaction. The HTTP SQL interface does not offer the same chained-transaction surface.
• A newly created index includes rows committed from transactions that were already open when the index was created.

Multi-statement requests and schema changes

On supported single-node deployments, a request containing several ordinary SQL statements runs as one transaction when no explicit transaction boundary separates them. A failing statement rolls back buffered writes. A BEGIN partway through that request includes its earlier buffered writes.

Schema changes apply immediately and are not transactional. PostgreSQL can commit earlier buffered writes at the schema change; HTTP SQL can leave them pending while applying the DDL. A rollback can discard pending data writes but cannot undo the schema change, and may report applied_at_once. Keep DDL separate from application transactions and inspect the actual response or notice. Multi-node and replicated-consensus behavior has additional limits.

Keys, values and expressions

• UPDATE … SET column = NULL stores NULL instead of substituting the column’s default.
• Supported foreign-key checks use the stored row after defaults and conversion. Foreign-key cascades are supported within their documented statement limits.
• Upserts with generated or default primary keys assign the appropriate key to each row. A row skipped by ON CONFLICT DO NOTHING must still meet NOT NULL and CHECK constraints.
• Calendar intervals work in date ranges, and date comparisons use date semantics.
• JSON comparisons follow PostgreSQL behavior, including over CTEs, derived tables and views. This does not establish complete PostgreSQL numeric or JSON-expression parity.

Handle the reported error

Known differences to test

• Recent engine fixes cover scalar JSON-path reads through joins. Test nested documents, casts and null behavior on your deployed build; this does not imply full PostgreSQL JSON compatibility.
• TRUNCATE and DROP apply immediately and are not undone by rollback.
• Changing a column used to compute a generated primary key through ON CONFLICT DO UPDATE is refused. Test generated foreign keys and cascading updates on the exact deployed build; a generated column does not establish complete PostgreSQL constraint parity.
• Use database grants for access control as well as transaction modes. PostgreSQL READ ONLY and HTTP /sql have different contracts: HTTP refuses a read-only transaction mode it does not enforce. Do not infer read-only enforcement from a successful read or a client label.
• Use SAVEPOINT inside an active transaction. Check the result of chained commit/rollback before issuing the next statement; do not depend on permissive behavior from an older build.
• Complete numeric precision and PostgreSQL behavior for special numeric values and all mathematical functions are not established.

Use Workbench and the console to inspect errors, and test the exact statements and connection surface your application uses.