SQLite to PostgreSQL Converter: Syntax Guide & Examples

Why Convert SQLite to PostgreSQL?

SQLite works well for prototyping and small applications, but PostgreSQL is the natural choice when you need concurrent access, user authentication, advanced data types, and production-grade reliability. Many teams start with SQLite during development (Django and Rails both default to it) and migrate to PostgreSQL for deployment.

PostgreSQL offers features that SQLite cannot provide: JSONB with GIN indexes, array columns, full-text search with ranking, table partitioning, logical replication, and role-based access control. For any application serving multiple users simultaneously, PostgreSQL is the right upgrade.

The migration is straightforward because PostgreSQL supports most of SQL that SQLite does, and tools like pgloader automate the entire process. The main areas of attention are data types (SQLite's flexible typing vs PostgreSQL's strict typing), date/time handling, and auto-increment columns.

Key Syntax Differences

Feature	SQLite	PostgreSQL
Auto-increment	INTEGER PRIMARY KEY AUTOINCREMENT	SERIAL PRIMARY KEY
Type system	Type affinity (flexible)	Strict type enforcement
Boolean	INTEGER (0/1)	BOOLEAN (TRUE/FALSE)
Date/time	TEXT / INTEGER	DATE / TIMESTAMP
String concatenation	||	|| (same)
Current time	datetime('now')	NOW() / CURRENT_TIMESTAMP
UPSERT	INSERT OR REPLACE	ON CONFLICT DO UPDATE
GLOB	GLOB 'pattern'	LIKE or ~ (regex)
Random	RANDOM()	RANDOM() (same)
JSON support	json_extract()	-> / ->> operators

Common Conversions with Examples

AUTOINCREMENT to SERIAL

-- SQLite
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

-- Or with PostgreSQL 10+ IDENTITY syntax:
CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

Date and Time Handling

-- SQLite
SELECT datetime('now');
SELECT date('now', '+30 days');
SELECT strftime('%Y-%m', created_at) FROM orders;
SELECT * FROM orders WHERE created_at > datetime('now', '-7 days');

-- PostgreSQL
SELECT NOW();
SELECT CURRENT_DATE + INTERVAL '30 days';
SELECT TO_CHAR(created_at, 'YYYY-MM') FROM orders;
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Boolean Handling

-- SQLite
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    done INTEGER DEFAULT 0
);
INSERT INTO tasks (done) VALUES (1);
SELECT * FROM tasks WHERE done = 1;

-- PostgreSQL
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    done BOOLEAN DEFAULT FALSE
);
INSERT INTO tasks (done) VALUES (TRUE);
SELECT * FROM tasks WHERE done = TRUE;

UPSERT Syntax

-- SQLite
INSERT OR REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

-- PostgreSQL (same ON CONFLICT syntax!)
INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

Good news: PostgreSQL's ON CONFLICT syntax is almost identical to SQLite's. The only difference is that PostgreSQL uses uppercase EXCLUDED by convention. INSERT OR REPLACE does not exist in PostgreSQL; always use ON CONFLICT.

JSON Handling

-- SQLite
SELECT json_extract(metadata, '$.name') FROM events;
SELECT * FROM events WHERE json_extract(metadata, '$.type') = 'click';

-- PostgreSQL (JSONB)
SELECT metadata->>'name' FROM events;
SELECT * FROM events WHERE metadata->>'type' = 'click';

-- With GIN index for fast JSON queries
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

Group Concatenation

-- SQLite
SELECT department, GROUP_CONCAT(name, ', ') FROM employees GROUP BY department;

-- PostgreSQL
SELECT department, STRING_AGG(name, ', ') FROM employees GROUP BY department;

Data Type Mapping

SQLite Type	PostgreSQL Type	Notes
INTEGER	INTEGER or BIGINT	Check actual data range
REAL	DOUBLE PRECISION	8-byte float
TEXT	TEXT or VARCHAR(n)	PostgreSQL TEXT has no size limit
BLOB	BYTEA	Binary data
NUMERIC	NUMERIC	Same name, add precision
TEXT (date)	DATE or TIMESTAMP	ISO 8601 strings work directly
INTEGER (timestamp)	TIMESTAMPTZ	Convert with to_timestamp()
INTEGER (boolean)	BOOLEAN	0 to FALSE, 1 to TRUE
TEXT (JSON)	JSONB	Binary JSON with indexing
VARCHAR(n)	VARCHAR(n)	Same (SQLite ignores length)

Step-by-Step Migration Guide

1. Use pgloader (recommended). The simplest approach is a single command: pgloader sqlite:///path/to/db.sqlite3 postgresql:///target_db. It handles schema conversion, data transfer, and index creation automatically.
2. Manual schema export. If you prefer manual control, run .schema in SQLite CLI to get CREATE TABLE statements. Edit them: replace AUTOINCREMENT with SERIAL, TEXT date columns with TIMESTAMP, and INTEGER booleans with BOOLEAN.
3. Export data as CSV. Use SQLite's .mode csv and .output to export each table. Import into PostgreSQL with COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true).
4. Convert boolean values. If your SQLite data uses 0/1 for booleans, the COPY command handles this automatically when the target column is BOOLEAN in PostgreSQL. If using INSERT statements, replace 0 with FALSE and 1 with TRUE.
5. Update application queries. Replace datetime('now') with NOW(), GROUP_CONCAT with STRING_AGG, RANDOM() returns different range in PostgreSQL (use RANDOM() with different scaling), and json_extract() with ->>.
6. Reset sequences. After data import, reset auto-increment sequences to the correct value: SELECT setval('users_id_seq', (SELECT MAX(id) FROM users)).

For SQL fundamentals, see What is SQL. For join syntax, see SQL Joins Explained. See also: SQLite to MySQL.

Convert Automatically with AI2SQL

Paste your SQLite query into the SQL Converter tool and get correct PostgreSQL syntax with SERIAL, BOOLEAN, NOW(), JSONB operators, and proper types. No need to remember the differences.

Try the converter free. Browse all converter tools.

Frequently Asked Questions

How do I convert SQLite AUTOINCREMENT to PostgreSQL?

Replace INTEGER PRIMARY KEY AUTOINCREMENT with SERIAL PRIMARY KEY (or BIGSERIAL for large tables). PostgreSQL's SERIAL creates an implicit sequence. For PostgreSQL 10+, you can also use INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY for more control.

Can I use pgloader to migrate from SQLite to PostgreSQL?

Yes. pgloader is the recommended tool. A single command handles the entire process: pgloader sqlite:///path/to/db.sqlite3 postgresql:///target_db. It automatically maps SQLite types to PostgreSQL types, handles data transfer, and creates indexes. You can customize type mapping with a configuration file.

What are the main advantages of PostgreSQL over SQLite?

PostgreSQL supports concurrent read/write access, user authentication and role-based permissions, advanced data types (JSONB, arrays, UUID, INET), full-text search with ranking, table partitioning, and replication for high availability. SQLite is limited to single-writer access and stores everything in one file with no authentication.