SQLite vs PostgreSQL: Differences, Tested Examples and When to Use Each

Ninad Pathak

October 7, 2026

PostgreSQL and SQLite are close enough in their SQL that apps get developed on one and deployed on the other. They still disagree on details like types and booleans, and those details break queries. In this article I’ll walk you through the differences with the same statements run on both, and when to pick each one.

TLDR

SQLite is a library inside your program, with the whole database in one file. PostgreSQL is a database server that many clients connect to.
Choose SQLite when the database lives on the same machine as the code and writes can take turns.

Choose PostgreSQL when several servers share the data and write to it at the same time.
SQLite copies PostgreSQL’s upsert syntax and JSON operators, so a lot of SQL moves between them unchanged.
A sqlite3 .dump loads into PostgreSQL with one error, but the IDs need an identity column afterwards.

What is SQLite?

SQLite is a relational database engine that runs inside your program, with the database stored in one file. Its documentation says it “does not compete with client/server databases. SQLite competes with fopen()” (Appropriate Uses For SQLite).

What is PostgreSQL?

PostgreSQL is an open-source relational database server. Clients connect to it over the network, and it handles many sessions at once with multiversion concurrency control, so “reading never blocks writing and writing never blocks reading” (PostgreSQL concurrency control).

SQLite vs PostgreSQL at a glance

The differences that decide the choice:

	SQLite	PostgreSQL
Architecture	Library inside your program; no server process (SQLite is serverless)	A server process that clients connect to
Typing	Flexible by default; `STRICT` tables enforce types	Column types enforced
Concurrency	Many readers; one writer at a time per file	MVCC; many sessions write at once
Maximum size	About 281 TB per database with 64 KiB pages	Unlimited database size; 32 TB per table with 8 KiB blocks
Users and permissions	None in SQL; file permissions only	Roles with `GRANT` and `REVOKE`
Licence	Public domain	PostgreSQL License, similar to BSD or MIT

Differences that matter

To show you the differences, I will run each pair of statements on SQLite and on PostgreSQL with its default settings. Lines starting with sqlite> or postgres=# show the statement, and the lines after them are its output. There are no speed benchmarks, because results depend on your hardware and workload.

Column types: flexible in SQLite, enforced in PostgreSQL

SQLite stores the type with each value, not with the column (Datatypes in SQLite). So an INTEGER column accepts text unless the table is STRICT. PostgreSQL rejects it:

sqlite> CREATE TABLE items (id INTEGER PRIMARY KEY, qty INTEGER);
sqlite> INSERT INTO items (qty) VALUES ('ten');
sqlite> SELECT id, qty, typeof(qty) FROM items;
id  qty  typeof(qty)
--  ---  -----------
1   ten  text

sqlite> CREATE TABLE items_strict (id INTEGER PRIMARY KEY, qty INTEGER) STRICT;
sqlite> INSERT INTO items_strict (qty) VALUES ('ten');
Error: stepping, cannot store TEXT value in INTEGER column items_strict.qty (19)

postgres=# CREATE TABLE items (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, qty integer);
postgres=# INSERT INTO items (qty) VALUES ('ten');
ERROR:  invalid input syntax for type integer: "ten"
LINE 1: INSERT INTO items (qty) VALUES ('ten');
                                        ^

If SQLite is your test database and PostgreSQL your production one, STRICT tables make SQLite fail the same way PostgreSQL does.

Identity columns

In SQLite, INTEGER PRIMARY KEY assigns IDs automatically. In PostgreSQL, the standard form is GENERATED ALWAYS AS IDENTITY (PostgreSQL CREATE TABLE), which the PostgreSQL examples on this page use.

ALTER TABLE

“SQLite supports a limited subset of ALTER TABLE”. It covers renaming a table or a column. You can also add or drop a column. SQLite 3.53.0 (April 2026) added ALTER COLUMN … SET NOT NULL and DROP NOT NULL (SQLite ALTER TABLE). Changing a column’s type still isn’t supported. In the sqlite3 shell:

sqlite> ALTER TABLE items ALTER COLUMN qty TYPE bigint;
Error: in prepare, near "ALTER": syntax error
  ALTER TABLE items ALTER COLUMN qty TYPE bigint;
                    ^--- error here

And on SQLite 3.53.4, through Node.js’s built-in node:sqlite, with this script:

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
console.log("SQLite", db.prepare("SELECT sqlite_version() AS v").get().v);
db.exec("CREATE TABLE items (id INTEGER PRIMARY KEY, qty INTEGER)");
for (const sql of [
  "ALTER TABLE items ALTER COLUMN qty SET NOT NULL",
  "ALTER TABLE items ALTER COLUMN qty TYPE bigint",
]) {
  try {
    db.exec(sql);
    console.log("ok:   ", sql);
  } catch (e) {
    console.log("error:", sql, "->", e.message);
  }
}
console.log(db.prepare("SELECT sql FROM sqlite_schema WHERE name = 'items'").get().sql);

SQLite 3.53.4
ok:    ALTER TABLE items ALTER COLUMN qty SET NOT NULL
error: ALTER TABLE items ALTER COLUMN qty TYPE bigint -> near "TYPE": syntax error
CREATE TABLE items (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL)

PostgreSQL changes the type in place:

postgres=# ALTER TABLE items ALTER COLUMN qty TYPE bigint;
postgres=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'items' ORDER BY ordinal_position;
 column_name | data_type
-------------+-----------
 id          | integer
 qty         | bigint
(2 rows)

In SQLite, the documented workaround is to create a new table with the new definition and copy the rows across. Then drop the old table and rename the new one.

Upsert

“UPSERT in SQLite follows the syntax established by PostgreSQL, with generalizations” (SQLite UPSERT), so the same statement runs on both. Both also support RETURNING:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, logins INTEGER NOT NULL DEFAULT 0);
sqlite> INSERT INTO users (name) VALUES ('ada');
sqlite> INSERT INTO users (name, logins) VALUES ('ada', 1) ON CONFLICT(name) DO UPDATE SET logins = users.logins + 1 RETURNING id, name, logins;
id  name  logins
--  ----  ------
1   ada   1

postgres=# CREATE TABLE users (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE, logins integer NOT NULL DEFAULT 0);
postgres=# INSERT INTO users (name) VALUES ('ada');
postgres=# INSERT INTO users (name, logins) VALUES ('ada', 1) ON CONFLICT(name) DO UPDATE SET logins = users.logins + 1 RETURNING id, name, logins;
 id | name | logins
----+------+--------
  1 | ada  |      1
(1 row)

There’s one difference. In the DO UPDATE clause, PostgreSQL needs the qualified column (users.logins). The bare name is an error there, while SQLite accepts it:

postgres=# INSERT INTO users (name, logins) VALUES ('ada', 1) ON CONFLICT(name) DO UPDATE SET logins = logins + 1;
ERROR:  column reference "logins" is ambiguous
LINE 1: ...ada', 1) ON CONFLICT(name) DO UPDATE SET logins = logins + 1...
                                                             ^

sqlite> INSERT INTO users (name, logins) VALUES ('ada', 1) ON CONFLICT(name) DO UPDATE SET logins = logins + 1 RETURNING id, name, logins;
id  name  logins
--  ----  ------
1   ada   2

Booleans

SQLite has no boolean storage class. TRUE is the integer 1, and a BOOLEAN column accepts any value (Datatypes in SQLite). PostgreSQL’s boolean type accepts a fixed set of spellings, including 'yes', and rejects anything else (PostgreSQL boolean type):

sqlite> CREATE TABLE flags (id INTEGER PRIMARY KEY, active BOOLEAN); INSERT INTO flags (active) VALUES (TRUE), ('yes'); SELECT id, active, typeof(active) FROM flags;
id  active  typeof(active)
--  ------  --------------
1   1       integer
2   yes     text

postgres=# CREATE TABLE flags (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, active boolean); INSERT INTO flags (active) VALUES (TRUE), ('yes'); SELECT id, active, pg_typeof(active) FROM flags;
 id | active | pg_typeof
----+--------+-----------
  1 | t      | boolean
  2 | t      | boolean
(2 rows)

postgres=# INSERT INTO flags (active) VALUES ('maybe');
ERROR:  invalid input syntax for type boolean: "maybe"
LINE 1: INSERT INTO flags (active) VALUES ('maybe');
                                           ^

Dates

SQLite stores the impossible date 30 February as text, and its date() function rolls it forward to 2 March. PostgreSQL rejects it:

sqlite> CREATE TABLE events (id INTEGER PRIMARY KEY, happened_on DATE); INSERT INTO events (happened_on) VALUES ('2026-02-30'); SELECT id, happened_on, date(happened_on) AS normalised FROM events;
id  happened_on  normalised
--  -----------  ----------
1   2026-02-30   2026-03-02

postgres=# CREATE TABLE events (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, happened_on date); INSERT INTO events (happened_on) VALUES ('2026-02-30');
ERROR:  date/time field value out of range: "2026-02-30"
LINE 1: ...n date); INSERT INTO events (happened_on) VALUES ('2026-02-3...
                                                             ^

LIKE and case

SQLite’s LIKE ignores case for ASCII letters but not for other characters (SQLite LIKE). PostgreSQL’s LIKE is case-sensitive, and ILIKE is its case-insensitive form (PostgreSQL pattern matching):

sqlite> SELECT 'SQLite' LIKE 'sqlite' AS ascii_like, 'Æ' LIKE 'æ' AS non_ascii_like;
ascii_like  non_ascii_like
----------  --------------
1           0

postgres=# SELECT 'SQLite' LIKE 'sqlite' AS like_result, 'SQLite' ILIKE 'sqlite' AS ilike_result;
 like_result | ilike_result
-------------+--------------
 f           | t
(1 row)

JSON

SQLite added -> and ->> in 3.38.0, and “the SQLite implementation of -> and ->> strives to be compatible with both MySQL and PostgreSQL” (SQLite JSON functions). SQLite accepts a JSON path or a plain key, and PostgreSQL takes a key on a json or jsonb value:

sqlite> SELECT '{"user": {"name": "ada"}}' -> '$.user' ->> '$.name' AS name;
name
----
ada
sqlite> SELECT '{"user": {"name": "ada"}}' -> 'user' ->> 'name' AS name;
name
----
ada

postgres=# SELECT '{"user": {"name": "ada"}}'::jsonb -> 'user' ->> 'name' AS name;
 name
------
 ada
(1 row)

SQLite also has a JSONB format, but “the on-disk format for SQLite’s JSONB is not the same as PostgreSQL’s” (SQLite JSON functions). Don’t copy binary values between them.

Concurrency

PostgreSQL lets many sessions write at once. SQLite in WAL mode lets readers and a writer work at the same time, but “there can only be one writer at a time” (Write-Ahead Logging). For lock errors in SQLite, see database is locked.

Size limits

An SQLite database tops out at about 281 TB with 64 KiB pages, or about 17.5 TB with the default 4 KiB pages (Limits in SQLite). PostgreSQL lists database size as unlimited, and a single table at 32 TB with its default 8 KiB block size (PostgreSQL limits).

Permissions and licensing

SQLite has no GRANT or REVOKE. The only access control is the operating system’s file permissions on the database file (SQL features SQLite does not implement). PostgreSQL has roles and grants privileges at levels from databases down to columns (PostgreSQL GRANT). SQLite is in the public domain (SQLite is public domain). PostgreSQL is released under the PostgreSQL License, “a liberal Open Source license, similar to the BSD or MIT licenses” (PostgreSQL licence).

When to use SQLite and when to use PostgreSQL

SQLite’s documentation (Appropriate Uses For SQLite) recommends a client/server database such as PostgreSQL when:

The data is on a different machine from the code that runs the SQL. A database engine reads far more data from disk than it returns, so it belongs next to the disk.

Many writers must write at the same instant. “SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns.”
The data may reach the terabyte range. Past a terabyte, the page suggests “a centralized client/server database”.

For device-local storage with low writer concurrency, the same page recommends SQLite. You can use both: SQLite for local development or an embedded cache, and PostgreSQL as the shared production database.

Moving a SQLite database into PostgreSQL

The simplest route is to pipe sqlite3 .dump into psql. I tried it on a small table. Only the PRAGMA line fails, and the rows load:

$ sqlite3 mig.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, active INTEGER DEFAULT 1);
INSERT INTO users VALUES(1,'ada',1);
INSERT INTO users VALUES(2,'linus',1);
COMMIT;
$ sqlite3 mig.db .dump | psql -d demo
ERROR:  syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;
        ^
BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
COMMIT

The trap comes next. INTEGER PRIMARY KEY arrives as a plain integer column, so a new row without an ID fails:

postgres=# INSERT INTO users (name) VALUES ('grace');
ERROR:  null value in column "id" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (null, grace, 1).

Turn the column into an identity column that starts after the highest existing ID. PostgreSQL’s ALTER COLUMN … ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY does that, and like other column defaults it only affects “subsequent INSERT and UPDATE commands” (PostgreSQL ALTER TABLE):

postgres=# ALTER TABLE users ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 3);
postgres=# INSERT INTO users (name) VALUES ('grace') RETURNING id, name;
 id | name
----+-------
  3 | grace
(1 row)

On a real database, also check the type differences above, such as dates and booleans, before you load the data.

Conclusion

SQLite and PostgreSQL share a lot of SQL, including upsert and JSON operators. Use SQLite when one machine owns the data, and PostgreSQL when many clients or servers share it. If you develop on SQLite and deploy on PostgreSQL, use STRICT tables and qualified upsert columns. Plan the ID fix for when you migrate.