Migrate SQLite to PostgreSQL
Certified — live-tested pathThe 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.
Free tier · no credit card
SQLite vs PostgreSQL
SQLite
the embedded database that ships inside everything.
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.
| SQLite | PostgreSQL |
|---|---|
| INTEGER | BIGINT |
| REAL | DOUBLE PRECISION |
| TEXT | TEXT |
| BLOB | BYTEA |
| NUMERIC | DOUBLE PRECISION |
| BOOLEAN | BOOLEAN |
| DATETIME | TIMESTAMP |
| 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
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
Masking PII in Flight: Production Data Without Production Risk
Mask during the migration, not after, so real PII never sits in a lower environment.
Reference
Compared with other tools
Ready to move SQLite to PostgreSQL?
Set up in under two minutes. Validation and rollback included.
Start Free Migration