sqlstreak

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 LIKE for case-insensitive search in PostgreSQL. Use ILIKE or LOWER().

  • 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.

Practise it now