PostgreSQL compatibility

pg-datahike implements PostgreSQL's application-facing SQL and wire-protocol boundary on top of Datahike. It is intended to let PostgreSQL clients and applications use Datahike without adopting a separate query protocol. It is not a drop-in replacement for the PostgreSQL server or its internal storage, planning, administration, replication, and extension machinery.

Compatibility is developed and tested at the boundaries applications observe:

Area	Compatibility target
Wire protocol	Startup, authentication, simple and extended queries, prepared statements, portals, result metadata, and SQLSTATE-bearing errors used by supported clients
SQL	A growing application-oriented subset of PostgreSQL DDL, DML, expressions, relational queries, transactions, and type semantics
Types and functions	PostgreSQL-compatible behavior for admitted built-ins; coverage varies by type and function family
Catalogs	The catalog relations and metadata queries needed by supported drivers, tools, and ORMs
Datahike semantics	Datahike remains the storage and transaction system; PostgreSQL behavior that conflicts with its model may be unsupported or documented as different

PostgreSQL-specific server features such as procedural languages, server-side extensions, replication, roles and server administration, physical storage features, and planner controls are generally outside this compatibility target. Their syntax may still be recognized where that is useful, but applications must not assume that arbitrary PostgreSQL SQL or extensions will run unchanged.

Sequence defaults and inherited columns

INSERT and COPY reserve sequence defaults independently of the row transaction. The reserved values remain consumed if a constraint rejects a row or the caller rolls back. Explicit NULL suppresses a default. Ordinary inheritance copies a SERIAL column's nextval default, but does not copy identity generation; the inherited NOT NULL constraint still applies.

VALUES, DEFAULT VALUES, and target-column defaults in INSERT SELECT are evaluated one candidate at a time. If an early candidate fails, later target defaults are not requested; already reserved sequence values remain consumed. This applies to plain INSERT and ON CONFLICT, including prepared statements and transactions. COPY also evaluates and validates source rows in order: a failed row consumes defaults evaluated for that row, but no later row's defaults. Its executor batches speculative validation, while the rows become durable only after the complete COPY succeeds. CopyFail, a later-row error, and transaction rollback therefore leave no copied row behind. Text and CSV input retain incomplete UTF-8 code points across wire frames and reject malformed input with SQLSTATE 22021. A raw, unquoted DEFAULT marker is evaluated in COPY column-list order; omitted-column defaults follow in physical column order. Stable time defaults, cancellation, and statement_timeout cover the complete COPY stream, not each wire frame separately.

Exact evaluation order remains open for volatile expressions produced by a non-trivial INSERT SELECT source. That path still needs the shared lazy source/candidate executor before pg-datahike can claim the same cutoff point as PostgreSQL for every bulk write.

Earlier pg-datahike versions stored SERIAL and GENERATED BY DEFAULT identity with the same identity metadata. Their original declarations cannot be recovered from that metadata. Existing columns retain their recorded identity behavior; they are not automatically reclassified as SERIAL. For a legacy SERIAL column whose default should apply to inherited inserts, explicitly restore its default with ALTER TABLE parent ALTER COLUMN id SET DEFAULT nextval('parent_id_seq'). Fresh SERIAL declarations store an ordinary nextval default and report no identity generation in the PostgreSQL catalogs.

Concurrent catalog changes

Writes validate the catalog metadata used to prepare them against the writer's current snapshot. If that metadata has changed, the statement or transaction can fail with SQLSTATE 40001; retry the whole transaction from a fresh snapshot. This also applies to metadata changed through Datahike's native API.

Simple literal INSERTs into ordinary scalar tables can tolerate unrelated catalog changes when their target metadata is unchanged. Other statement forms use a conservative whole-catalog check and may require a retry even when the concurrent change concerns another table. Sequence counter advances and ordinary row writes do not count as catalog changes.

Wire security

pg-datahike implements PostgreSQL's conventional SSLRequest negotiation and cleartext-password authentication exchange. The password exchange is intended to run inside TLS: start-server rejects a non-loopback bind unless both TLS and a password authenticator are configured, and it rejects a plaintext StartupMessage before asking for a password. Standard client modes including sslmode=require and sslmode=verify-full are tested with pgjdbc and psql.

Authentication is a deployment boundary rather than a PostgreSQL role system. The wire layer delegates verification to an application callback, with a fixed user map available for small deployments. Authenticated users are not yet mapped to Datahike's per-database authorization model. PostgreSQL HBA rules, SCRAM, MD5, client-certificate authentication, GSS encryption, and direct TLS negotiation are outside the current surface.

Compatibility guarantees

Supported behavior is established by focused tests, client and framework integration suites, and admitted slices of PostgreSQL's upstream regression suite. Upstream tests are also used as a discovery corpus: a regression file may exercise both useful application behavior and PostgreSQL server internals, so using it during development does not imply that the whole file is supported.

Within the supported surface, pg-datahike aims to:

• return PostgreSQL-compatible rows, column metadata, type OIDs, and NULL semantics;
• behave consistently across simple and extended query protocols;
• reject unsupported or invalid operations with explicit, SQLSTATE-bearing errors rather than internal failures or silent approximations;
• preserve Datahike's transaction and durability guarantees.

Compatibility is still evolving. Before replacing PostgreSQL for an existing application, run that application's own integration and migration tests against pg-datahike. Please report a minimal query, expected PostgreSQL behavior, actual pg-datahike behavior, and client/driver details for any discrepancy.

Maintainers can find the upstream-suite workflow, admission criteria, and PostgreSQL source map in the regression harness documentation.