SQLite to PostgreSQL Data Migration

Migrating to PostgreSQL from SQLite: what changes in the target

If the PostgreSQL table does not exist, dbSlice stops and shows a CREATE TABLE translated for PostgreSQL, for your DBA to review and run. By default it creates nothing in your databases. dbSlice reads the type you declared in SQLite, with its length and precision. PostgreSQL is stricter than SQLite about the values, so check these:

Text keeps its declared length. A SQLite VARCHAR(100) is suggested as VARCHAR(100). Columns declared as TEXT, or with no length, become TEXT, which in PostgreSQL costs nothing and can be a key or indexed.
INTEGER becomes BIGINT. SQLite integers are 64-bit, while PostgreSQL INTEGER is 32-bit, so the suggested DDL uses BIGINT.
Decimals keep their precision. DECIMAL(10,2) is suggested as NUMERIC(10,2). A decimal declared without precision becomes NUMERIC, which keeps every digit.
Booleans. SQLite stores them as 0 and 1. Declare the column BOOLEAN, and dbSlice converts 0/1 into false/true.
Dates stored as text or numbers. When the PostgreSQL column is DATE or TIMESTAMP, dbSlice converts ISO text and Unix time on the way. Julian day numbers (REAL) need a SELECT on the source: SELECT id, datetime(created_jd) AS created_at, … FROM orders.
Mixed values in one column. Numbers stored as text are converted when the target is numeric. A value that is not a number at all stops the row with the real error, so clean it in a SELECT first.
Ids and sequences. The SQLite ids are copied as they are. After the copy the PostgreSQL sequence must move past the highest id: tick Adjust the id generator afterwards (needs ALTER), or run the command dbSlice shows.
Upsert runs as INSERT … ON CONFLICT on the key.

How dbSlice controls the load

Reads the SQLite file ordered by the key.
Fast loads with COPY: Initial load and Insert new use PostgreSQL's binary COPY. If a value does not match the column type, nothing from that batch is written and dbSlice writes it again with normal INSERTs, so the real error is shown.
Matches rows by a key you choose. Modes: Initial load, then Upsert, Insert new, Update only or incremental by column (updated_at or the row id).
Checks before writing with Record Diff and on-screen checks, and proves the result with Compare and the full list of divergent keys.
Masks on the way for development and test copies, keeping keys and foreign keys intact.
Export instead of load: when you only need a file, the same app exports a SQLite table to CSV, JSON, XML or HTML.

Step by step: convert a table from SQLite to PostgreSQL

Open the SQLite database file and connect to PostgreSQL.
Pick the source table (or a SELECT, as in the julian-date example) and the target table.
Choose the key column.
Choose the mode: Initial load, then Upsert or Insert new.
Run Record Diff, then the copy, or save it as a job for the CLI and the Windows Task Scheduler.

Moving data between SQLite and PostgreSQL in both directions

The other way, dbSlice exports a PostgreSQL table to a SQLite database file, for a portable copy or an offline app. For SQL Server, see SQLite to SQL Server. Moving a bigger system? See MySQL and MariaDB to PostgreSQL.

FAQ

Does SQLite need a server or a driver?

No. dbSlice opens the database file directly.

Are triggers and views converted?

No. dbSlice moves data; views can be a source through a SELECT.

Are deletes copied by incremental runs?

No. Run Compare or a reload before the switch.

Is there a free version?

Yes, on the Microsoft Store: it connects, browses, compares and exports to CSV. Writing to a target needs a Desktop or Server license.

Data migration from SQLite to PostgreSQL