Skip to content
IntoSQL home
Convert a file

Migration guides

PostgreSQL to SQLite migration guide

Convert a PostgreSQL dump into a SQLite script with the same tables, keys, indexes and data: how to dump, convert, load, and every rule the converter applies.

View as Markdown

IntoSQL reads a plain PostgreSQL dump and writes one SQLite script with the same tables, columns, keys, indexes, foreign keys, defaults, counters and rows. It reads the dump itself, without a database server, and lists in the report every value SQLite can't hold as it is and every object it doesn't carry. The script loads on SQLite 3.31 and later.

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

Dump for the best result

Any plain dump from pg_dump or pg_dumpall from PostgreSQL 10 on works, as .sql or .sql.gz, with COPY rows (the default) or --inserts:

Terminal
pg_dump --no-owner --no-privileges shop | gzip > shop.sql.gz
  • --no-owner --no-privileges leave out roles and grants, which the converter skips anyway.
  • A custom or directory archive (-Fc, -Fd) is binary: pg_restore -f shop.sql shop.backup turns it into plain SQL without a server. The converter says so when it gets an archive (dump_archive).
  • pg_dumpall writes every database in one file; each database's tables are named after it, so nothing collides.

A dump made with --data-only still converts: the types are read from the values, and the report says the schema was missing (no_schema). Partitioned tables arrive as one table with every partition's rows (partitions_dropped).

Convert

In the browser, open PostgreSQL to SQLite or the converter on the home page, drop the dump and choose SQLite. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump 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:

Terminal
intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to sqlite --output shop.sqlite.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 dump converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "sqlite"}.

Load into SQLite

Terminal
sqlite3 -bail shop.sqlite < shop.sqlite.sql

The script creates each table with its keys, copies the rows and adds indexes and counters, all in one transaction with syncing off, so it runs at disk speed and a load that stops leaves no partial tables. Schemas other than public become a prefix on their tables' names (app.orders is app_orders). Foreign keys are declared in each table and checked once the rows are in: the script ends with PRAGMA foreign_key_check, which lists any row that points to a missing row instead of failing the load. Turn foreign keys on in each connection that writes (PRAGMA foreign_keys = ON), as SQLite expects.

Or load it straight in

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

Terminal
intosql load shop.sql.gz --into shop.db

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, and a file the load created is removed if it fails. Loading into SQLite is free. See Loading.

What isn't converted

Views, materialized views, functions, procedures, triggers, rules, row security policies, extensions and types are written in PostgreSQL's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in SQLite.

Data types

PostgreSQL to SQLite 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.

In PostgreSQL In SQLite Why Report line
NOT NULL columns with values that become NULL NOT NULL dropped A column whose values had to become NULL can't keep NOT NULL, or the load would stop. not_null_relaxed
citext and nondeterministic collations exact comparison SQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters. case_insensitive_collation
sequences not owned by a column not created SQLite has no sequences. sequence_not_converted
generated columns GENERATED ALWAYS AS, STORED or VIRTUAL as in the source SQLite 3.31 and later compute generated columns the same two ways. None
generated columns with functions SQLite lacks a plain column with the values A formula that can't be written for SQLite leaves a plain column with the dump's values and a report line. generated_not_translated
defaults with functions SQLite lacks, and defaults from a sequence no default A default that can't be written for SQLite is left out and reported. default_not_translated
CHECK constraints translated, inline in CREATE TABLE SQLite only takes checks in CREATE TABLE. None
CHECK constraints with functions SQLite lacks not created A check that can't be written for SQLite is left out and reported. check_skipped
CHECK ... NOT VALID not created PostgreSQL never checked the rows that were there before, and SQLite would check them while loading. check_not_valid
gin, gist, brin and hash indexes, partial and expression indexes not created IntoSQL writes b-tree indexes on columns for SQLite; create the others again after loading. index_skipped
foreign keys in CREATE TABLE, loaded unchecked, then listed by PRAGMA foreign_key_check SQLite takes foreign keys only in CREATE TABLE and checks them only with PRAGMA foreign_keys on. The rows load unchecked and the script ends listing any row without a parent. None
foreign keys to tables the dump doesn't create not created A key can't name a table that isn't there. foreign_key_skipped
partitioned tables one table with every partition's rows The rows are the same. partitions_dropped
schemas one file, tables named after their schema first A SQLite file is one database, so the schema name keeps tables with the same name apart; tables in public keep their names. schemas_merged
views, functions, procedures, triggers, rules, policies, extensions and types listed in the report Their code is written in PostgreSQL's language, so they are listed for you to recreate. not_converted_objects
dumps without CREATE TABLE (--data-only) types read from the values A data-only dump has only rows, so the types come from the values. no_schema
expressions in INSERT values NULL A converter that doesn't run a database can't evaluate them. expression_value
text decoded twice (ñ) copied as it is The dump holds exactly these characters, so they are copied; the report points them out. mojibake_suspected