Migrating from Postgres

1. Connection


postgresql://<dk_key_id>:<secret>@pg.uk-lon-1.dodil.io:5432/<bucket>?sslmode=require

dbname is the bare bucket name. Do not prefix it with your org.
user / password are an API key id and secret, a service account’s client id and secret, or any user name with a bearer JWT as the password.
sslmode=require at minimum; verify-full recommended (sslrootcert=system on libpq 16+). Never prefer.
search_path, application_name, TimeZone and client_encoding startup parameters are accepted; only statement_timeout changes behaviour.

dodil data connect <bucket> -o psql prints a ready DSN. Details on Connect.

2. Schema

In Postgres you had	Here	Action
`INTEGER` / `INT` columns	Stored 64-bit; catalog reports `BIGINT`.	None. Accept or ignore the autogenerate retype diff.
`SERIAL` / `BIGSERIAL` / `GENERATED AS IDENTITY`	A 64-bit autoincrement: `BIGINT` with a generated, monotonic id; no sequence object. `SMALLSERIAL` stays 16-bit.	Read ids with `RETURNING`. Do not rely on ids being small or consecutive.
`VARCHAR(n)` / `CHAR(n)`	Enforced since 0.1.43: overlength is `22001`. `CHAR(n)` is not padded.	None, unless you relied on padding.
`NUMERIC(p,s)`	Exact to 38 digits on every path (literal, bind, `COPY`); arithmetic and rounding match Postgres since 0.1.43. `p > 38` refused. Bare `NUMERIC` becomes `(38,9)` with a `NOTICE`.	Cap precision at 38 and always declare `(p,s)`.
`SMALLINT`	16-bit on the wire (`int2`); out of range is `22003`, never wrapped since 0.1.37.	None.
`TIMESTAMP` / `TIMESTAMPTZ`	One type: UTC microseconds, advertised as `timestamptz`. `TIMESTAMP WITHOUT TIME ZONE` raises a `NOTICE`.	Store UTC. Convert for display in the application.
`TEXT[]`, `INT[]`	Stored natively; advertised on the wire as `text` literals.	Parse `{…}` client-side; bind arrays as parameters normally.
`JSON` / `JSONB`	`jsonb` with the usual `->`, `->>` operators.	None.
`UUID`	Native type, wire OID `uuid` since 0.1.37; a non-UUID value is `22P02`. UUID primary keys work as in Postgres.	Generate with `gen_random_uuid()`, or `DEFAULT GENERATE_UUID()` on the column.
`REFERENCES` / `FOREIGN KEY`	Enforced on insert: a child naming a missing parent is `23503`. Not checked on delete (orphans are legal), nor on an autocommit `UPDATE` of the FK column, nor by `MERGE`. Not introspected back to the ORM.	Keep them. Delete children before parents in the application.
`… ON DELETE CASCADE` / `SET NULL` / `SET DEFAULT`, `DEFERRABLE`	Refused at DDL `0A000`.	Remove the option from the model or hand-edit the migration; cascade in the application.
`CHECK (…)`	Enforced for literal predicates; refused at DDL for subqueries or expressions.	Move complex checks into the application; keep literal ones.
`CREATE INDEX ON t (a, b)` (non-unique)	Refused.	One index per column, or make it `UNIQUE`.
Triggers, stored functions	Refused `0A000` with the roadmap in the message.	`CHECK` for validation; ingest rules, pipelines or Actions for reactions.
Views, rules, sequences	Refused `42601` (`statement is not part of the tables SQL surface`).	`CREATE TABLE … AS SELECT` for views; `SERIAL` for ids.
`DEFAULT 0` on a `NUMERIC` column	Refused at DDL `42703`.	Drop the default and supply the value on insert.
Schemas	One namespace per bucket. `public.` is stripped.	Use one bucket per schema if you need isolation.
`ALTER TABLE … RENAME`	Refused.	Copy-and-drop migration.

Full detail: Data types, Constraints, Schema changes.

3. Writes

Every OLTP table needs a PRIMARY KEY. It is how updates find rows and how conflicts are detected.
INSERT of an existing key → 23505, always. For idempotent writes: INSERT … ON CONFLICT (pk) DO UPDATE SET …, ON CONFLICT DO NOTHING (first wins), or UPSERT INTO.
UPDATE and DELETE require a WHERE. WHERE TRUE for all rows; TRUNCATE to empty a table.
INSERT … SELECT maps columns by position.
RETURNING works on every DML statement, including inside a transaction.
COPY t FROM STDIN (text or CSV) works; COPY TO does not.

4. Transactions

Postgres serialises writers with row locks. This engine detects conflicts at COMMIT and never blocks.

Postgres habit	Here
`SELECT … FOR UPDATE` blocks other writers until you commit	Records the rows read. Another committed write to any of them makes your `COMMIT` fail `40001`. Same guarantee against lost updates, no waiting.
Deadlock detection, `lock_timeout`	Nothing to deadlock on.
`SERIALIZABLE` / `REPEATABLE READ`	Accepted, no effect. Snapshot reads plus commit-time guard.
Retry on `40001` only if you chose `SERIALIZABLE`	Retry on `40001` always. Wrap every transaction that writes in a retry loop.
`SAVEPOINT`	Works the same.
Advisory locks (`pg_advisory_lock`), `LOCK TABLE`	Fail (`42P01` and `42601`). Use a guarded row (`FOR UPDATE` on a key) as the mutex.

A ready loop and the ledger pattern are on Transactions & concurrency.

5. Reads

Reads see every committed write immediately, on any connection; there is no replica lag and no read-only replica endpoint to route to. Inside an open transaction you see your own staged writes. Query syntax is DuckDB’s, a superset of what most Postgres applications use: CTEs, window functions, LATERAL, FILTER, QUALIFY, list and struct functions. Postgres-only spellings that differ are listed on Functions & operators.

6. Catalog

ORMs and migration tools introspect through information_schema.tables / .columns and a set of emulated pg_catalog probes. They see:

one schema, public; every table with table_type = 'BASE TABLE';
columns with data_type, is_nullable, column_default, character_maximum_length and datetime_precision since 0.1.43;
primary keys and unique indexes; no foreign keys, views, sequences, functions, enums or extensions (all answer empty). information_schema.table_constraints is not served.

Anything else against pg_catalog fails with 42P01. Full surface on Catalog.

7. ORM and driver matrix

Verified by the conformance suite against the live adapter (connect, migrate, CRUD, typed round-trips, transactions across tables, rollback):

ORM / driver	Status	Notes
SQLAlchemy 2 (psycopg 3)	Supported	`create_all`, `Session` add/flush/commit, `NUMERIC → Decimal`, `JSONB → dict`, cross-table commit and rollback. Optimistic `UPDATE` row counts are real.
psycopg 3	Supported	Extended protocol, typed binds, `RETURNING`, `COPY`, server-side cursors, notices via `add_notice_handler`.
GORM (pgx driver)	Supported	`AutoMigrate`, CRUD, transactions. `INTEGER` fields migrate as `BIGINT`.
SQLx (Rust)	Supported	Compile-time checked queries, `JSONB → serde_json::Value`, transactions.
node-postgres (`pg`)	Supported	Parameterised queries, `RETURNING`, `notice` events, array binds.
Prisma	Supported	`db push`, `migrate dev`, model CRUD including `deleteMany({})`. Emitted FKs are accepted unenforced.
Django ORM	Supported	`migrate`, QuerySets, `set_config('TimeZone', …)` at connect is answered. Declare `DateTimeField` as UTC. `on_delete=models.CASCADE` emits `ON DELETE CASCADE`, which is refused: edit the migration to drop the clause and keep the cascade in Python.
Alembic	Supported	Upgrade and downgrade, including `DROP CONSTRAINT` of an FK. Suppress FK diffs in autogenerate; strip `ondelete=` options.
pgx (direct), JDBC, asyncpg, Rails/ActiveRecord	Not suite-verified	Standard wire features they need are present; not in the conformance run. Report what you find.

8. Coming from another database

Coming from	Equivalent here
Postgres `INSERT … ON CONFLICT (pk) DO UPDATE`	Works as written; `SET` takes any expression.
MySQL `INSERT … ON DUPLICATE KEY UPDATE`	Accepted as written, same lowering.
BigQuery `CREATE TABLE … AS SELECT`, `MERGE`	Same syntax.
Databricks / Delta `RESTORE TABLE … TO VERSION AS OF`	Same syntax, including `TIMESTAMP AS OF`. The tail after the target is discarded.
DuckDB	Same dialect plus transactions, constraints, `FOR UPDATE`, pgvector operators and `RESTORE`.
pgvector `<->`, `<#>`, `<=>`, `CREATE INDEX … USING hnsw`	Supported. See Vector.
Spark / Iceberg `MERGE INTO`	Same `WHEN MATCHED` / `WHEN NOT MATCHED` shape.

Checklist

Swap the DSN; confirm SELECT version() returns PostgreSQL 16.0 (Dodil Tables adapterd).
Run your migrations. Read every NOTICE. Remove triggers, views and functions; strip ON DELETE / DEFERRABLE options from foreign keys; split composite non-unique indexes.
Add a retry-on-40001 wrapper around every writing transaction.
Find plain INSERTs that relied on upsert or on ignoring duplicates; convert them to ON CONFLICT.
Own the delete side of every foreign key: delete children before parents, or guard the parent with FOR UPDATE and check.
Cap NUMERIC precision at 38; treat every timestamp as UTC.
Run the application’s test suite against a throwaway bucket before pointing production at one.