postgresql
sqlite

Migrate PostgreSQL to SQLite

Certified — live-tested path

The converter maps 18 PostgreSQL column types to SQLite. 8 of them change kind on the way (boolean becomes INTEGER, date becomes TEXT, timestamp becomes TEXT, and more); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.

Start PostgreSQL → SQLite Migration

Free tier · no credit card

PostgreSQL vs SQLite

postgresql

PostgreSQL

the most advanced open-source relational database.

sqlite

SQLite

the embedded database that ships inside everything.

PostgreSQL to SQLite data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

PostgreSQLSQLite
integerINTEGER
bigintINTEGER
smallintINTEGER
booleanINTEGER
numeric(10,2)NUMERIC
realREAL
double precisionREAL
varchar(255)TEXT
textTEXT
char(10)TEXT
dateTEXT
timestampTEXT
timestamptzTEXT
timeTEXT
byteaBLOB
jsonTEXT
jsonbTEXT
uuidTEXT

Example: PostgreSQL to SQLite schema conversion

PostgreSQL

CREATE TABLE orders (
  id            bigserial PRIMARY KEY,
  customer_id   integer NOT NULL,
  total         numeric(10,2) NOT NULL,
  is_gift       boolean NOT NULL DEFAULT false,
  notes         text,
  metadata      jsonb,
  placed_at     timestamptz NOT NULL
);

SQLite

CREATE TABLE IF NOT EXISTS "orders" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "customer_id" INTEGER NOT NULL,
    "total" NUMERIC NOT NULL,
    "is_gift" INTEGER NOT NULL DEFAULT 0,
    "notes" TEXT,
    "metadata" TEXT,
    "placed_at" TEXT NOT NULL
);

One writer at a time

PostgreSQL handles many concurrent writers. SQLite allows exactly one at a time and makes the others wait or fail with database is locked. That is fine for an embedded copy, a test fixture or a single-user app, and wrong for a multi-user write workload, whatever the data looks like.

Turn on WAL mode, PRAGMA journal_mode = WAL, so readers keep working while one connection writes, and set a busy timeout so a waiting writer retries instead of failing straight away.

boolean becomes INTEGER, timestamps become text

boolean becomes INTEGER holding 0 or 1. SQLite 3.23 and later understands TRUE and FALSE as those numbers, so most boolean queries keep working unchanged.

date, timestamp and timestamptz all become TEXT. timestamptz values are converted to UTC and written without an offset, so their text order and their time order agree; whatever reads them back has to know they are UTC, because the text no longer says so.

jsonb and arrays become text

json and jsonb become TEXT, and arrays are serialised to JSON text. SQLite's json functions query them in place, and from SQLite 3.38 the -> and ->> operators exist too: metadata->>'plan' works on both engines, with ->> returning an SQL value rather than JSON.

There is no GIN index. To search inside an array, use json_each() in a join, and index an expression such as json_extract(metadata, '$.plan') where a lookup needs to be fast.

PostgreSQL → SQLite conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

  • Arrays (integer[], text[]) are preserved natively on PostgreSQL targets and serialized to JSON elsewhere.
  • JSONB, UUID, INET and other rich types fall back to portable representations with every downgrade recorded in the fidelity report.
  • SERIAL/identity sequences are recreated and reseeded on the target.
  • High-precision decimals land in TEXT columns on purpose — SQLite's NUMERIC affinity would silently convert them to lossy REAL.
  • Unsigned BIGINT values beyond the signed 64-bit range are stored as exact text rather than corrupted floats.
  • Dates and times are stored as ISO-8601 text, the SQLite convention.

Frequently asked questions

Is PostgreSQL to SQLite migration production-ready in DBShifts?

Yes. PostgreSQL to SQLite is a certified pair: it passes the live certification suite on real PostgreSQL and SQLite servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.

What happens to PostgreSQL types that SQLite doesn't have?

Each gets the nearest SQLite type the converter treats as safe: boolean becomes INTEGER, date becomes TEXT, timestamp becomes TEXT, timestamptz becomes TEXT, time becomes TEXT. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

Can I keep PostgreSQL and SQLite in sync after the initial migration?

Yes, until you cut over. DBShifts uses logical decoding through a replication slot, which needs wal_level = logical and the wal2json plugin on the server. With zero-downtime mode on, the slot is created before the first row is copied, so it holds every change made during the copy.

Which PostgreSQL data types need a second look when moving to SQLite?

The table above carries a note on 3 of them: timestamptz (TEXT), bytea (BLOB), jsonb (TEXT). Analysis flags the same columns on your own schema before any data moves.

Go deeper

Ready to move PostgreSQL to SQLite?

Set up in under two minutes. Validation and rollback included.

Start Free Migration