Convert SQLite to PostgreSQL
Drop a SQLite database file (.db, .sqlite, .sqlite3) or the SQL that sqlite3's .dump writes, and download a script for PostgreSQL with the same tables, keys, indexes and data. Each column gets the type its values fit, and every value PostgreSQL can't hold as it is is listed in the report.
18 rows converted, no fixes, none rejected
Tables in shop_2024_sqlite.sqlSQLite dump · written by sqlite3 · utf-8
customers8 rows
7 columns id bigint not null name text not null email text not null country text not null is_active boolean not null signed_up date created_at timestamp(0) not null orders10 rows
9 columns id bigint not null customer_id bigint not null status text not null total numeric(10,2) not null items integer not null ordered_at timestamp(0) not null shipped_at timestamp(0) note text receipt bytea
Not converted
- View order_totals
- Trigger orders_shipped
Each database writes these in its own language. Rewrite them for PostgreSQL.
-- Written by IntoSQL from a SQLite dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';
CREATE TABLE customers (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name text NOT NULL,
email text NOT NULL,
country text DEFAULT 'US' NOT NULL,
is_active boolean DEFAULT true NOT NULL,
signed_up date,
created_at timestamp(0) DEFAULT LOCALTIMESTAMP NOT NULL,
CONSTRAINT customers_check1 CHECK (char_length(country) = 2)
);
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
customer_id bigint NOT NULL,
status text DEFAULT 'pending' NOT NULL,
total numeric(10,2) NOT NULL,
items integer DEFAULT 1 NOT NULL,
ordered_at timestamp(0) NOT NULL,
shipped_at timestamp(0),
note text,
receipt bytea,
CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);
COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1 Ana Martínez ana@example.com MX t 2023-01-15 2023-01-15 10:24:00
2 Zoë Müller zoe@example.de DE t 2023-02-03 2023-02-03 08:11:42
3 José Peña jose@example.es ES f 2023-03-21 2023-03-21 19:05:17
4 李雷 lilei@example.cn CN t 2023-05-09 2023-05-09 02:40:00
5 Noah O'Brien noah@example.ie IE t \N 2023-06-30 14:00:00
6 Amélie Laurent amelie@example.fr FR t 2024-01-02 2024-01-02 09:30:00
7 Ravi Patel ravi@example.in IN f 2024-02-29 2024-02-29 23:59:59
8 Sofia Rossi 🌻 sofia@example.it IT t 2024-04-12 2024-04-12 12:12:12
\.
COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, receipt) FROM stdin;
1 1 shipped 129.9 3 2024-03-01 10:00:00 2024-03-02 16:30:00 \N \N
2 2 paid 54 1 2024-03-02 11:15:00 \N Gift wrap 🎁 \N
3 1 cancelled 12.5 1 2024-03-03 09:45:00 \N Customer changed their mind \N
4 4 shipped 980 12 2024-03-05 02:10:00 2024-03-07 08:00:00 请小心轻放 \\x25504446
5 5 pending 45.75 2 2024-03-08 17:20:00 \N Leave at the door;\nring twice \N
6 6 shipped 310.4 5 2024-03-10 13:00:00 2024-03-11 09:00:00 Fragile: "glass" \N
7 8 paid 76.2 2 2024-03-11 18:45:00 \N \N \N
8 2 paid 19.99 1 2024-03-12 08:00:00 \N Paid at the counter \N
9 3 shipped 64 2 2024-03-14 15:30:00 \N Shipping date never recorded \N
10 7 paid 1499 1 2024-03-15 21:05:00 \N C:\\Users\\ravi\\invoice.pdf \N
\.
-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_email ON customers (email);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE customers ADD CONSTRAINT customers_email UNIQUE USING INDEX customers_email;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE NOT VALID;
DO $f$ BEGIN
ALTER TABLE orders VALIDATE CONSTRAINT orders_fkey;
EXCEPTION WHEN foreign_key_violation THEN
RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_fkey';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 9), 9223372036854775807)::bigint, false);
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 11), 9223372036854775807)::bigint, false);
Fixes
Nothing needed fixing.
Needs a look
- 1 text column compares ignoring case in the source; PostgreSQL compares exactly, so unique keys and searches may behave differently. Use a case-insensitive collation or column type on the columns that need it.
- 2 views, triggers, routines or events aren't converted; the report lists each one. Recreate them by hand in PostgreSQL.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from SQLite to PostgreSQL
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none)
the type every value in the column fits
SQLite keeps any value in any column, so the declared type is a hint: each column gets the type all of its values fit, and a column holding mixed values keeps their text.
BOOLEAN, BOOL
boolean
When every value is 0 or 1; a column that also holds other values keeps them as numbers or text.
TEXT, VARCHAR(n), CHAR(n), CLOB
varchar(n) when every value fits the declared length, else text
SQLite never enforces a length; a declared length holds only when every value fits it.
DECIMAL(p,s), NUMERIC
the exact type when every value fits it, else a double
SQLite stores these as integers or doubles; a declared precision holds only when the values fit it.
BLOB, and columns holding bytes
bytea
Bytes stay bytes; text and numbers in the same column are kept as their bytes.
DATE, DATETIME, TIMESTAMP holding ISO 8601 text
date, timestamp
When every value is a date or a date and time; epoch numbers and other text keep their values as they are.
JSON holding JSON text
jsonb
When every value is a JSON object or array.
Infinity in REAL columns
Infinity, as PostgreSQL's double keeps it
PostgreSQL's doubles hold infinity.
INTEGER PRIMARY KEY, AUTOINCREMENT
GENERATED BY DEFAULT AS IDENTITY, continuing after the highest id or sqlite_sequence's
Only a column declared exactly INTEGER PRIMARY KEY is the rowid; every row keeps its id, and the counter starts after the highest id ever handed out.
COLLATE NOCASE
exact comparison
PostgreSQL compares text exactly unless told otherwise; NOCASE columns lose ignoring case.
Report line: case_insensitive_collation
generated columns
STORED
PostgreSQL 13 to 17 only store generated columns; the formula is the same.
Report line: virtual_as_stored
generated columns with functions IntoSQL doesn't write, and the || operator
a plain column
A formula that can't be written leaves a plain column and a report line; the dump has no values for generated columns.
Report line: generated_not_translated
defaults with functions IntoSQL doesn't write (strftime, randomblob)
no default
A default that can't be written is left out and reported; CURRENT_TIMESTAMP and datetime('now') are written.
Report line: default_not_translated
CHECK constraints
translated
The same checks, tested as each row loads.
CHECK constraints with functions IntoSQL doesn't write
not created
A check that can't be written is left out and reported.
Report line: check_skipped
partial and expression indexes
not created
IntoSQL writes indexes on whole columns; create the others again after loading.
Report line: index_skipped
foreign keys (unenforced unless PRAGMA foreign_keys=ON)
foreign keys, NOT VALID then validated
SQLite checks foreign keys only when told to, so rows without a parent are common: they load, and the key is left unchecked with a warning instead of stopping the load.
foreign keys to tables the dump doesn't create
not created
A key can't name a table that isn't there.
Report line: foreign_key_skipped
primary keys holding NULL, and NOT NULL columns with values that become NULL
NOT NULL dropped
SQLite lets a primary key other than the rowid hold NULL; the column can't be NOT NULL in the target, or the load would stop.
Report line: not_null_relaxed
views, triggers and virtual tables (FTS, R*Tree)
listed in the report
Their code is written in SQLite's own language, and virtual tables need SQLite's modules; they are listed for you to recreate. Their internal tables aren't copied.
Report line: 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 and the columns are numbered.
Report line: no_schema
expressions in INSERT values other than the text functions dumps write
NULL
unistr(), replace(), char() and || are evaluated as the shell writes them; other expressions can't be without a database.
Report line: expression_value
NUL characters in text
removed
The sqlite3 shell and Python cut text at a NUL character when they dump it, so only what precedes it is in the dump. PostgreSQL text can't hold one either.
Report line: nul_removed
text that isn't valid UTF-8
the bad bytes as the replacement character
Dumps write such text as raw bytes; the script is UTF-8.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The dump holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
| In SQLite | In PostgreSQL | Report line |
|---|---|---|
| declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none) | the type every value in the column fitsSQLite keeps any value in any column, so the declared type is a hint: each column gets the type all of its values fit, and a column holding mixed values keeps their text. | |
| BOOLEAN, BOOL | booleanWhen every value is 0 or 1; a column that also holds other values keeps them as numbers or text. | |
| TEXT, VARCHAR(n), CHAR(n), CLOB | varchar(n) when every value fits the declared length, else textSQLite never enforces a length; a declared length holds only when every value fits it. | |
| DECIMAL(p,s), NUMERIC | the exact type when every value fits it, else a doubleSQLite stores these as integers or doubles; a declared precision holds only when the values fit it. | |
| BLOB, and columns holding bytes | byteaBytes stay bytes; text and numbers in the same column are kept as their bytes. | |
| DATE, DATETIME, TIMESTAMP holding ISO 8601 text | date, timestampWhen every value is a date or a date and time; epoch numbers and other text keep their values as they are. | |
| JSON holding JSON text | jsonbWhen every value is a JSON object or array. | |
| Infinity in REAL columns | Infinity, as PostgreSQL's double keeps itPostgreSQL's doubles hold infinity. | |
| INTEGER PRIMARY KEY, AUTOINCREMENT | GENERATED BY DEFAULT AS IDENTITY, continuing after the highest id or sqlite_sequence'sOnly a column declared exactly INTEGER PRIMARY KEY is the rowid; every row keeps its id, and the counter starts after the highest id ever handed out. | |
| COLLATE NOCASE | exact comparisonPostgreSQL compares text exactly unless told otherwise; NOCASE columns lose ignoring case. | case_insensitive_collation |
| generated columns | STOREDPostgreSQL 13 to 17 only store generated columns; the formula is the same. | virtual_as_stored |
| generated columns with functions IntoSQL doesn't write, and the || operator | a plain columnA formula that can't be written leaves a plain column and a report line; the dump has no values for generated columns. | generated_not_translated |
| defaults with functions IntoSQL doesn't write (strftime, randomblob) | no defaultA default that can't be written is left out and reported; CURRENT_TIMESTAMP and datetime('now') are written. | default_not_translated |
| CHECK constraints | translatedThe same checks, tested as each row loads. | |
| CHECK constraints with functions IntoSQL doesn't write | not createdA check that can't be written is left out and reported. | check_skipped |
| partial and expression indexes | not createdIntoSQL writes indexes on whole columns; create the others again after loading. | index_skipped |
| foreign keys (unenforced unless PRAGMA foreign_keys=ON) | foreign keys, NOT VALID then validatedSQLite checks foreign keys only when told to, so rows without a parent are common: they load, and the key is left unchecked with a warning instead of stopping the load. | |
| foreign keys to tables the dump doesn't create | not createdA key can't name a table that isn't there. | foreign_key_skipped |
| primary keys holding NULL, and NOT NULL columns with values that become NULL | NOT NULL droppedSQLite lets a primary key other than the rowid hold NULL; the column can't be NOT NULL in the target, or the load would stop. | not_null_relaxed |
| views, triggers and virtual tables (FTS, R*Tree) | listed in the reportTheir code is written in SQLite's own language, and virtual tables need SQLite's modules; they are listed for you to recreate. Their internal tables aren't copied. | not_converted_objects |
| dumps without CREATE TABLE (--data-only) | types read from the valuesA data-only dump has only rows, so the types come from the values and the columns are numbered. | no_schema |
| expressions in INSERT values other than the text functions dumps write | NULLunistr(), replace(), char() and || are evaluated as the shell writes them; other expressions can't be without a database. | expression_value |
| NUL characters in text | removedThe sqlite3 shell and Python cut text at a NUL character when they dump it, so only what precedes it is in the dump. PostgreSQL text can't hold one either. | nul_removed |
| text that isn't valid UTF-8 | the bad bytes as the replacement characterDumps write such text as raw bytes; the script is UTF-8. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe dump holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
Load the script into PostgreSQL
The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.
psql -v ON_ERROR_STOP=1 -d mydb -f sqlite.postgresql.sqlForeign keys are added as NOT VALID and then validated. If a row points to a row that doesn't exist, the script tells you which constraint, instead of failing half way.
For a large dump, create the database first and run the script with psql from the same machine as the server.
IntoSQL Pro loads the file straight into PostgreSQL from the desktop app or the command line, without writing a script.
Dump SQLite for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- The database file itself converts as it is, read page by page in your browser. Close the app that writes to it first, so no change is left behind in its -wal file.
- sqlite3 app.db .dump > app.sql writes the same database as SQL, which converts too, as .sql or .sql.gz.
- DB Browser for SQLite writes that SQL with File, Export, Database to SQL file, and so does Python's iterdump().
What isn't converted
Views and triggers are written in SQLite's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for PostgreSQL. Virtual tables, such as FTS5 and R*Tree, are listed too, and their shadow tables are left out.
Questions about SQLite to PostgreSQL
Not answered here? Write to us from the contact page and a person replies. Contact page
Is my dump uploaded?
No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.
How large can the dump be?
The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.
Can I convert the .db file itself?
Yes. Drop the .db, .sqlite or .sqlite3 file: it is read in your browser and never uploaded. Text with NUL characters and every digit of a double arrive as stored, which a .dump can cut short.
Which SQLite versions can the dump come from?
Database files from SQLite 3.0 on, in UTF-8 or UTF-16, and dumps from the sqlite3 shell's .dump or Python's iterdump(). Every test file and dump comes from sqlite3 3.31 to 3.54, STRICT and WITHOUT ROWID tables included, and is compared with its source cell by cell.
How are column types decided when SQLite doesn't enforce them?
The declared type is a hint and the values decide: a column becomes integer, numeric, double precision, date, timestamp, boolean, bytea or text when every value fits it. A column holding mixed values keeps their text, and the report says which.
Are INTEGER PRIMARY KEY and AUTOINCREMENT kept?
Yes. An INTEGER PRIMARY KEY becomes an identity column, every row keeps its id, and the next id continues after the highest one, or after sqlite_sequence's value with AUTOINCREMENT.
What about views and triggers?
They are listed in the report by name and not converted, because each database writes them in its own language.
Which PostgreSQL versions does the script load on?
PostgreSQL 13 to 18. The same script loads on each of them, stopping at the first error if anything goes wrong.