SQLite to PostgreSQL migration guide

IntoSQL reads a SQLite database file, or the SQL that SQLite's .dump writes, and turns it into one PostgreSQL script with the same tables, columns, keys, indexes, foreign keys, defaults, counters and rows. SQLite keeps any value in any column, so the declared type is a hint: each column gets the PostgreSQL type all of its values fit, and a column holding mixed values keeps their text. Every value PostgreSQL can't hold as it is, and every object it doesn't carry, is listed in the report. The script loads on PostgreSQL 13 and later.

Convert in the browser: SQLite to PostgreSQL opens the converter set up for this pair.

The file or a dump#

Drop the database file itself (.db, .sqlite, .sqlite3): it is read page by page, in UTF-8 or UTF-16, and text with NUL characters and every digit of a double arrive as stored. Close the app that writes to it first, so no change is left behind in its -wal file.

A dump converts too, as .sql or .sql.gz:

sqlite3 shop.db .dump > shop.sql

• DB Browser for SQLite writes the same SQL with File, Export, Database to SQL file, and so does Python's iterdump().
• .dump --nosys converts too. A --data-only dump has no CREATE TABLE, so its columns are named column_1, column_2 and so on, and the types come from the values.
• Every test file and dump comes from sqlite3 3.31 to 3.54, with STRICT and WITHOUT ROWID tables, and is compared with its source cell by cell.

Convert#

In the browser, open SQLite to PostgreSQL or the converter on the home page, drop the file and choose PostgreSQL. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the file is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

intosql inspect shop.db
intosql convert shop.db --to postgresql --output shop.postgresql.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a database file or a dump converts like any other file: open a conversion with its name and "output": {"dialect": "postgresql"}.

Load into PostgreSQL#

createdb shop
psql -v ON_ERROR_STOP=1 -d shop -f shop.postgresql.sql

The script creates the tables without keys, copies the rows with COPY, then adds keys, indexes, foreign keys and identities, so it loads fast and stops at the first error. An INTEGER PRIMARY KEY becomes an identity column that continues after the highest id, or after the value sqlite_sequence holds for an AUTOINCREMENT table. Text compares exactly, as in SQLite; a COLLATE NOCASE column is reported (case_insensitive_collation), since PostgreSQL has no collation that ignores only ASCII case.

Or load it straight in#

intosql load and the desktop app load the dump into PostgreSQL without a script, with the same tables, keys and indexes the script creates:

intosql load shop.db --into postgres://ana@localhost/shop

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, so a failed load leaves nothing. Loading into a database server is part of IntoSQL Pro, with a 14-day trial. See Loading.

What isn't converted#

Views and triggers are written in SQLite's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in PostgreSQL. Virtual tables, such as FTS5 and R*Tree, are listed too, and their shadow tables are left out.

Data types#

SQLite to PostgreSQL data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule#

Generated from the engine, where each rule is decided.