SQLite SQL vs PostgreSQL

Window functions, CTEs, UPSERT, RETURNING and JSON in SQLite — and where the two databases give different answers.

Modern SQLite supports most of the SQL from this course: CTEs, window functions, UPSERT, RETURNING and JSON functions. The details are where they differ, and the differences can produce different answers to the same question.

Differences worth knowing

Using SQLite in tests and PostgreSQL in production without running the tests on PostgreSQL

Run the integration tests against PostgreSQL itself (e.g. in a container)

Type affinity and date functions behave differently, so tests can pass on SQLite and fail in production.

What is date('2025-01-31', '+1 month') in SQLite, and what is DATE '2025-01-31' + INTERVAL '1 month' in PostgreSQL?

SQLite: 2025-03-03 (overflow normalized). PostgreSQL: 2025-02-28 (clamped to the end of the month) — both shown in this course's real outputs.