Convert PostgreSQL to SQLite
Drop a plain dump from pg_dump or pg_dumpall, as .sql or .sql.gz, and download a script for SQLite with the same tables, keys, indexes and data. Every value SQLite can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 3 fixes, none rejected
Tables in shop_2024_postgres.sqlPostgreSQL 18.6 dump · written by pg_dump · utf8mb4
customers8 rows2
8 columns id INTEGER not null name TEXT not null email TEXT not null country VARCHAR(2) not null is_active INTEGER not null tags TEXT not null signed_up DATE created_at TIMESTAMP not null orders10 rows1
10 columns id INTEGER not null customer_id INTEGER not null status TEXT not null total DECIMAL(10,2) not null items INTEGER not null ordered_at DATETIME not null shipped_at DATETIME note TEXT details TEXT delivery TEXT
Not converted
- View paid_orders
Each database writes these in its own language. Rewrite them for SQLite.
-- Written by IntoSQL from a PostgreSQL dump. Load it with: sqlite3 -bail <database> < <file>
PRAGMA foreign_keys = OFF;
PRAGMA synchronous = OFF;
BEGIN;
CREATE TABLE customers ( -- People who bought at least once
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL,
country VARCHAR(2) DEFAULT 'US' NOT NULL,
is_active INTEGER DEFAULT 1 NOT NULL,
tags TEXT NOT NULL,
signed_up DATE,
created_at TIMESTAMP DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')) NOT NULL,
CONSTRAINT customers_is_active_check CHECK (is_active IN (0, 1))
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
status TEXT DEFAULT 'pending' NOT NULL,
total DECIMAL(10,2) NOT NULL,
items INTEGER DEFAULT 1 NOT NULL,
ordered_at DATETIME NOT NULL,
shipped_at DATETIME,
note TEXT,
details TEXT,
delivery TEXT,
CONSTRAINT orders_status_check CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
CONSTRAINT orders_orders_total_check CHECK (total >= (0)),
CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers (id, name, email, country, is_active, tags, signed_up, created_at) VALUES
(1,'Ana Martínez','ana@example.com','MX',1,'["vip"]','2023-01-15','2023-01-15 10:24:00'),
(2,'Zoë Müller','zoe@example.de','DE',1,'[]','2023-02-03','2023-02-03 08:11:42'),
(3,'José Peña','jose@example.es','ES',0,'["wholesale","late payer"]','2023-03-21','2023-03-21 19:05:17'),
(4,'李雷','lilei@example.cn','CN',1,'[]','2023-05-09','2023-05-09 02:40:00'),
(5,'Noah O''Brien','noah@example.ie','IE',1,'[]',NULL,'2023-06-30 14:00:00'),
(6,'Amélie Laurent','amelie@example.fr','FR',1,'["vip"]','2024-01-02','2024-01-02 09:30:00'),
(7,'Ravi Patel','ravi@example.in','IN',0,'[]','2024-02-29','2024-02-29 23:59:59'),
(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'[]','2024-04-12','2024-04-12 12:12:12');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, delivery) VALUES
(1,1,'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,'{"gift": false}','1 day 06:30:00'),
(2,2,'paid',54.00,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁','{"gift": true, "wrap": "red"}',NULL),
(3,1,'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind',NULL,NULL),
(4,4,'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放','{"fragile": true}','2 days 05:50:00'),
(5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice',NULL,NULL),
(6,6,'shipped',310.40,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"','{"fragile": true}','20:00:00'),
(7,8,'paid',76.20,2,'2024-03-11 18:45:00',NULL,NULL,NULL,NULL),
(8,2,'shipped',19.99,1,'2024-03-12 08:00:00',NULL,'Shipped date set to infinity by an old import',NULL,NULL),
(9,3,'shipped',64.00,2,'2024-03-14 15:30:00','2024-03-15 10:00:00','Two parcels','{"parcels": [1, 2]}','18:30:00'),
(10,7,'paid',1499.00,1,'2024-03-15 21:05:00',NULL,'C:\Users\ravi\invoice.pdf',NULL,NULL);
-- Indexes, triggers and counters, after the rows so each index builds once.
CREATE UNIQUE INDEX customers_customers_email_key ON customers (email);
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
UPDATE sqlite_sequence SET seq = max(seq, 1000) WHERE name = 'customers';
INSERT INTO sqlite_sequence (name, seq) SELECT 'customers', 1000 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'customers');
UPDATE sqlite_sequence SET seq = max(seq, 5000) WHERE name = 'orders';
INSERT INTO sqlite_sequence (name, seq) SELECT 'orders', 5000 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'orders');
COMMIT;
-- Rows whose foreign key finds no parent, if any: the load doesn't stop for them.
PRAGMA foreign_key_check;
Fixes
- "created_at" of customers holds moments with a time zone; SQLite has no type that keeps one, so they are stored in UTC as TIMESTAMP.
- "tags" of customers is text[], which SQLite doesn't have, so it is TEXT with the same values.
- "delivery" of orders is interval, which SQLite doesn't have, so it is TEXT with the same values.
Needs a look
- 1 view, trigger, routine or event isn't converted; the report lists each one. Recreate them by hand in SQLite.
- The default of "tags" in customers can't be written for SQLite (a value its type can't hold), so the column has no default. Add the default by hand if new rows need it.
- The index idx_orders_details on orders uses an expression that can't be written for SQLite (a gin index), so it isn't created. Create the index by hand if queries need it.
- 1 value in "shipped_at" of orders is a date SQLite can't store, such as infinity, so it is NULL. SQLite stores years 1 to 9999 without infinity; replace those dates in the source if they matter.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from PostgreSQL to SQLite
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
boolean
INTEGER 0 or 1 with a CHECK
SQLite has no boolean type; its own true and false are 1 and 0.
varchar(n), char(n), text
VARCHAR(n) and TEXT, not enforced
SQLite keeps the declared type for tools to read but doesn't limit the length.
numeric(p,s) past 15 digits and numeric without a precision
TEXT
SQLite keeps about 15 significant digits in a number. Wider numbers are kept as text so every digit stays exact.
Report line: decimal_as_text
money
NUMERIC
SQLite has no money type. The amount is kept as a number, without the currency symbol the server's locale added.
Report line: type_replaced
interval
TEXT with PostgreSQL's text
SQLite has no interval type; the text keeps every part.
Report line: type_replaced
arrays (integer[], text[]...)
TEXT with a JSON array
SQLite has no arrays. A JSON array keeps every item and NULL, and SQLite's JSON functions read it.
Report line: type_replaced
ranges, geometric types, tsvector, xml, hstore, PostGIS, composite and extension types
TEXT with PostgreSQL's text
SQLite has no equivalent type, so each value keeps the text PostgreSQL wrote.
Report line: type_replaced
json, jsonb
TEXT
SQLite's JSON functions read JSON from text.
timestamp
DATETIME as ISO 8601 text
SQLite stores dates as text its date functions read.
timestamp with time zone
TIMESTAMP as ISO 8601 text in UTC
SQLite has no type that keeps an offset, so each moment is written in UTC: the same instant whatever offset the dump wrote.
Report line: moments_as_utc
time
TIME as text
SQLite keeps times as text, 24:00:00 included.
infinity, -infinity and BC dates
NULL
SQLite's date functions read years 0 to 9999 without infinity. Those values become NULL and the report counts them.
Report line: date_out_of_range
NaN and Infinity in numeric, real and double precision
NULL
SQLite stores NaN as NULL and its infinity doesn't survive every tool, so those values become NULL and the report counts them.
Report line: invalid_value_null
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.
Report line: not_null_relaxed
enum types
TEXT with a CHECK of its labels
Text with a check keeps the same values.
enum columns holding values outside their labels
TEXT without a check
Values the labels don't name would fail the check, so the check isn't created.
Report line: enum_check_skipped
uuid, inet, cidr, macaddr
TEXT
SQLite has no such types; the text keeps every value.
bytea
BLOB
Bytes stay bytes.
bit(n), bit varying
INTEGER with the bits' value up to 63 bits; TEXT digits beyond
A bit string is stored as the number its bits make while it fits a signed integer.
oid, xid, cid
INTEGER with a CHECK that it isn't negative
SQLite's 64-bit integers hold every value.
citext and nondeterministic collations
exact comparison
SQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters.
Report line: case_insensitive_collation
serial, identity
INTEGER PRIMARY KEY AUTOINCREMENT, continuing after the highest id
SQLite counts a whole integer primary key, and AUTOINCREMENT keeps ids from being reused, as a sequence does.
serial and identity columns that aren't the whole primary key
INTEGER without a counter
SQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs.
Report line: counter_dropped
sequences not owned by a column
not created
SQLite has no sequences.
Report line: 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.
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.
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.
Report line: default_not_translated
CHECK constraints
translated, inline in CREATE TABLE
SQLite only takes checks in CREATE TABLE.
CHECK constraints with functions SQLite lacks
not created
A check that can't be written for SQLite is left out and reported.
Report line: check_skipped
CHECK ... NOT VALID
not created
PostgreSQL never checked the rows that were there before, and SQLite would check them while loading.
Report line: 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.
Report line: 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.
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
partitioned tables
one table with every partition's rows
The rows are the same.
Report line: 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.
Report line: 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.
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.
Report line: no_schema
expressions in INSERT values
NULL
A converter that doesn't run a database can't evaluate them.
Report line: expression_value
types IntoSQL doesn't know
TEXT
Text holds any value; change the type after loading.
Report line: unknown_type
text that isn't valid in its encoding
the bad bytes as the replacement character
SQLite text 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 PostgreSQL | In SQLite | Report line |
|---|---|---|
| boolean | INTEGER 0 or 1 with a CHECKSQLite has no boolean type; its own true and false are 1 and 0. | |
| varchar(n), char(n), text | VARCHAR(n) and TEXT, not enforcedSQLite keeps the declared type for tools to read but doesn't limit the length. | |
| numeric(p,s) past 15 digits and numeric without a precision | TEXTSQLite keeps about 15 significant digits in a number. Wider numbers are kept as text so every digit stays exact. | decimal_as_text |
| money | NUMERICSQLite has no money type. The amount is kept as a number, without the currency symbol the server's locale added. | type_replaced |
| interval | TEXT with PostgreSQL's textSQLite has no interval type; the text keeps every part. | type_replaced |
| arrays (integer[], text[]...) | TEXT with a JSON arraySQLite has no arrays. A JSON array keeps every item and NULL, and SQLite's JSON functions read it. | type_replaced |
| ranges, geometric types, tsvector, xml, hstore, PostGIS, composite and extension types | TEXT with PostgreSQL's textSQLite has no equivalent type, so each value keeps the text PostgreSQL wrote. | type_replaced |
| json, jsonb | TEXTSQLite's JSON functions read JSON from text. | |
| timestamp | DATETIME as ISO 8601 textSQLite stores dates as text its date functions read. | |
| timestamp with time zone | TIMESTAMP as ISO 8601 text in UTCSQLite has no type that keeps an offset, so each moment is written in UTC: the same instant whatever offset the dump wrote. | moments_as_utc |
| time | TIME as textSQLite keeps times as text, 24:00:00 included. | |
| infinity, -infinity and BC dates | NULLSQLite's date functions read years 0 to 9999 without infinity. Those values become NULL and the report counts them. | date_out_of_range |
| NaN and Infinity in numeric, real and double precision | NULLSQLite stores NaN as NULL and its infinity doesn't survive every tool, so those values become NULL and the report counts them. | invalid_value_null |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| enum types | TEXT with a CHECK of its labelsText with a check keeps the same values. | |
| enum columns holding values outside their labels | TEXT without a checkValues the labels don't name would fail the check, so the check isn't created. | enum_check_skipped |
| uuid, inet, cidr, macaddr | TEXTSQLite has no such types; the text keeps every value. | |
| bytea | BLOBBytes stay bytes. | |
| bit(n), bit varying | INTEGER with the bits' value up to 63 bits; TEXT digits beyondA bit string is stored as the number its bits make while it fits a signed integer. | |
| oid, xid, cid | INTEGER with a CHECK that it isn't negativeSQLite's 64-bit integers hold every value. | |
| citext and nondeterministic collations | exact comparisonSQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters. | case_insensitive_collation |
| serial, identity | INTEGER PRIMARY KEY AUTOINCREMENT, continuing after the highest idSQLite counts a whole integer primary key, and AUTOINCREMENT keeps ids from being reused, as a sequence does. | |
| serial and identity columns that aren't the whole primary key | INTEGER without a counterSQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs. | counter_dropped |
| sequences not owned by a column | not createdSQLite has no sequences. | sequence_not_converted |
| generated columns | GENERATED ALWAYS AS, STORED or VIRTUAL as in the sourceSQLite 3.31 and later compute generated columns the same two ways. | |
| generated columns with functions SQLite lacks | a plain column with the valuesA 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 defaultA default that can't be written for SQLite is left out and reported. | default_not_translated |
| CHECK constraints | translated, inline in CREATE TABLESQLite only takes checks in CREATE TABLE. | |
| CHECK constraints with functions SQLite lacks | not createdA check that can't be written for SQLite is left out and reported. | check_skipped |
| CHECK ... NOT VALID | not createdPostgreSQL 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 createdIntoSQL 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_checkSQLite 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. | |
| foreign keys to tables the dump doesn't create | not createdA key can't name a table that isn't there. | foreign_key_skipped |
| partitioned tables | one table with every partition's rowsThe rows are the same. | partitions_dropped |
| schemas | one file, tables named after their schema firstA 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 reportTheir 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 valuesA data-only dump has only rows, so the types come from the values. | no_schema |
| expressions in INSERT values | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| types IntoSQL doesn't know | TEXTText holds any value; change the type after loading. | unknown_type |
| text that isn't valid in its encoding | the bad bytes as the replacement characterSQLite text 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 SQLite
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.
sqlite3 -bail mydb.sqlite < postgresql.sqlite.sqlForeign keys are declared in each table and the rows load with foreign key checks off. The script then lists any row that points to a row that doesn't exist, instead of failing half way.
For a large dump, load into a new file on a local disk. The script loads everything in one transaction with syncing off, so it runs at disk speed, and a load that stops half way leaves no partial tables.
The command line loads the file straight into a SQLite database, without writing a script. It's free.
Dump PostgreSQL for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --format=plain, pg_dump's default, writes SQL. A custom or directory archive (-Fc, -Fd) becomes SQL with pg_restore -f dump.sql, without a database server.
- --no-owner --no-privileges leave out roles and grants, which the converter skips anyway, so the dump is smaller.
- pg_dumpall dumps every database in one file; each one's tables are named after it, so nothing collides.
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 name, never dropped silently, so you can rewrite them for SQLite.
Questions about PostgreSQL to SQLite
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.
Does it read pg_dump -Fc archives?
Not directly: a custom archive is a binary format. pg_restore -f dump.sql dump.backup turns it into the plain SQL the converter reads, without connecting to a server. The converter says so when it gets an archive.
Which PostgreSQL versions can the dump come from?
Dumps written by pg_dump and pg_dumpall from PostgreSQL 10 on, with COPY or INSERT rows. Every test dump comes from PostgreSQL 13 and 18 and is compared with its source cell by cell.
Are serial and identity values kept?
A serial or identity column that is the table's whole primary key becomes INTEGER PRIMARY KEY AUTOINCREMENT, every row keeps its id, and the next id continues after the highest one. Any other keeps its values without a counter, and the report says so.
What happens to arrays, enums and other PostgreSQL types?
Arrays become JSON arrays, booleans 0 and 1, enums text with a check of their labels, and timestamps with a time zone UTC text. Wide numerics stay exact as text. Ranges, geometric types and composite types keep PostgreSQL's text.
What about functions, triggers and views?
They are listed in the report by name and not converted, because each database writes them in its own language.
Which SQLite versions does the script load on?
SQLite 3.31 and later. Every test dump loads on 3.31 and 3.54, stopping at the first error if anything goes wrong.