The Differences That Bite

SQLite is forgiving in ways Postgres is not, and the moment of migration is when you find out.

Types are advisory in SQLite. It will store the text "banana" in an INTEGER column without complaint. Postgres refuses. Data that was quietly wrong for months becomes an error on the day you migrate.

Foreign keys are off by default in SQLite unless you enable them. Under Postgres they are always enforced, so a reference pointing at a row that never existed surfaces immediately.

GROUP BY is stricter. SQLite will let you select a column that is neither grouped nor aggregated and quietly pick a row. Postgres refuses, which is better: a plausible wrong answer becomes an error.

The same statement, two databases

SQLite

-- SQLite: accepted
CREATE TABLE t (n INTEGER);
INSERT INTO t VALUES ('banana');

-- and it is really in there

PostgreSQL

-- PostgreSQL: rejected
CREATE TABLE t (n INTEGER);
INSERT INTO t VALUES ('banana');

-- ERROR: invalid input syntax
-- for type integer

Compare → Neither is a bug. SQLite chose flexibility deliberately. But it means a migration is also an audit, and the audit is usually where the surprises are.

Other differences worth knowing before you meet them.

String concatenation is || in both, but SQLite also allows + to do something unexpected with text.

Booleans are real in Postgres; SQLite stores 0 and 1.

Case sensitivity in LIKE differs; Postgres has ILIKE for case-insensitive matching.

Auto-incrementing keys use sequences in Postgres, and SERIAL or IDENTITY rather than AUTOINCREMENT.

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Practice Test →

← Back