sqlite
postgresql

Migrate SQLite to PostgreSQL

Certified — live-tested path

The converter maps 8 SQLite column types to PostgreSQL. One of them changes kind on the way (NUMERIC becomes DOUBLE PRECISION); 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 SQLite → PostgreSQL Migration

Free tier · no credit card

SQLite vs PostgreSQL

sqlite

SQLite

the embedded database that ships inside everything.

postgresql

PostgreSQL

the most advanced open-source relational database.

SQLite to PostgreSQL data type mapping

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

SQLitePostgreSQL
INTEGERBIGINT
REALDOUBLE PRECISION
TEXTTEXT
BLOBBYTEA
NUMERICDOUBLE PRECISION
BOOLEANBOOLEAN
DATETIMETIMESTAMP
VARCHAR(255)VARCHAR(255)

Example: SQLite to PostgreSQL schema conversion

SQLite

CREATE TABLE orders (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id   INTEGER NOT NULL,
  total         NUMERIC NOT NULL,
  is_gift       BOOLEAN NOT NULL DEFAULT 0,
  notes         TEXT,
  placed_at     DATETIME NOT NULL
);

PostgreSQL

CREATE TABLE IF NOT EXISTS "orders" (
    "id" BIGSERIAL,
    "customer_id" BIGINT NOT NULL,
    "total" DOUBLE PRECISION NOT NULL,
    "is_gift" BOOLEAN NOT NULL DEFAULT FALSE,
    "notes" TEXT,
    "placed_at" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
);

PostgreSQL validates text SQLite never checked

SQLite lets a TEXT value contain NUL characters. PostgreSQL rejects them, with invalid byte sequence for encoding UTF8: 0x00. By default DBShifts strips NUL bytes from text as rows are copied and keeps a per-column count of what it changed.

SQLite does not validate UTF-8 either, and bytes that are not valid UTF-8 cannot be read as text at all; they have to be repaired at the source. If a column really holds binary data stored as TEXT, move it to a BLOB, which becomes BYTEA, before the migration, so nothing about it is treated as text.

Typeless columns meet a typed database

PostgreSQL enforces types that SQLite only suggested. A column declared INTEGER that contains 'unknown' in a few rows fails with invalid input syntax for type bigint. SELECT typeof(col), count(*) FROM t GROUP BY 1 finds them, and DBShifts flags mixed-affinity columns during analysis so they surface before data moves.

NUMERIC columns become DOUBLE PRECISION, matching how SQLite stored them. For money, override the column to NUMERIC(12,2), which PostgreSQL keeps exact through every sum and average.

AUTOINCREMENT becomes BIGSERIAL

INTEGER PRIMARY KEY AUTOINCREMENT becomes BIGSERIAL, a BIGINT column backed by a sequence, as the worked example shows. After the load the sequence is set to the highest imported id, so the first new row does not collide.

The sequence is a separate object. A later bulk insert that supplies its own ids moves the data past the sequence without telling it, and the next default id collides. setval('orders_id_seq', (SELECT max(id) FROM orders)) is the fix, and it belongs in any import script you keep.

SQLite → PostgreSQL conversion notes

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

  • INTEGER columns are 64-bit — DBShifts maps them to BIGINT so large IDs don't overflow 32-bit INT targets.
  • Typeless and mixed-affinity columns (SQLite's dynamic typing) are inferred, stringified safely, and flagged.
  • NUMERIC affinity values are read exactly, not through float round-trips.
  • Bulk loads use sequence reseeding (setval to MAX) so SERIAL columns keep working after migration.
  • ENUMs from MySQL become native CREATE TYPE … AS ENUM — validation semantics preserved.
  • Foreign keys and indexes are applied after data load for dramatically faster transfers.

Frequently asked questions

Is SQLite to PostgreSQL migration production-ready in DBShifts?

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

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

Each gets the nearest PostgreSQL type the converter treats as safe: NUMERIC becomes DOUBLE PRECISION. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

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

Not continuously: SQLite keeps no change log to read. Incremental sync re-runs copy only the rows that changed, using a per-table sync key.

Go deeper

Ready to move SQLite to PostgreSQL?

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

Start Free Migration