PostgreSQL vs SQLite: SQL differences
Most everyday SQL works unchanged in PostgreSQL and SQLite. The differences cluster in a few places: dates, a handful of text functions, types and strictness. Every example below has a version for each. Switch the dialect above the code to compare.
Dates: extracting parts
PostgreSQL has a real DATE type with EXTRACT and to_char. SQLite stores dates as text and formats them with strftime.
SELECT name, strftime('%Y', arrived_on) AS year,
strftime('%Y-%m', arrived_on) AS month
FROM pets;Dates: arithmetic
In PostgreSQL you add integers to dates and subtract dates to get days. SQLite uses date() modifiers and julianday().
SELECT name, date(arrived_on, '+30 days') AS month_later,
CAST(julianday('2026-11-17') - julianday(arrived_on) AS INTEGER) AS days_here
FROM pets;Text functions
UPPER, LOWER, LENGTH, REPLACE, TRIM, SUBSTR and || are shared. Splitting text differs: PostgreSQL has split_part; in SQLite you combine instr and substr.
SELECT name, substr(name, 1, instr(name, ' ') - 1) AS first_name FROM customers;
Types and booleans
PostgreSQL enforces column types strictly and has a true BOOLEAN. SQLite uses flexible “type affinity” and stores booleans as 1 and 0, though it understands TRUE and FALSE. Run this in both: PostgreSQL shows true/false, SQLite shows 1/0.
SELECT name, adopted, adopted = true AS is_adopted FROM pets;
GROUP BY strictness
PostgreSQL rejects a selected column that's neither grouped nor aggregated. SQLite allows it and returns a value from an arbitrary row. Write queries that pass PostgreSQL's rule and they'll be correct in both.
Common mistakes
Relying on SQLite's loose typing, then moving to PostgreSQL and hitting type errors.
Using
LIKEfor case-insensitive search in PostgreSQL. UseILIKEorLOWER().Storing dates in SQLite in a non-ISO format like 03/01/2026, which then sorts incorrectly.
Questions
Which should I learn first?+
Learn standard SQL, which is most of it, in either. If you're aiming for analyst or backend jobs, practise in PostgreSQL; it's what most job listings mention.
Can SQLite handle real applications?+
Yes. It powers browsers, phones and many websites. It's ideal for embedded and single-server apps; PostgreSQL suits multi-user servers with heavy concurrent writes.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.