PostgreSQL vs SQLite
Game: the terminal of every chapter
PostgreSQL and SQLite both speak SQL, and most queries run the same on both. This page is about the places where they differ: what each one is, when to pick which, and the differences a learner actually runs into, each shown on the bakery database.
The terminal in this handbook and in the game runs SQLite inside your browser, but it reads your SQL by PostgreSQL's rules. So if a query from a SQLite tutorial is refused here, or a query you wrote here fails on a PostgreSQL server at work, one of the sections below explains why. A table at the end sums it all up.
A server and a file
PostgreSQL is a database server. It is a program that runs on its own, often on another machine, and waits for connections, by default on port 5432. Your application, a command-line client such as psql or a desktop tool connects to it, sends a query and gets the rows back. The server keeps the data in its own directory of files, which you never open yourself. It serves many connections at once, knows who each user is and what they may do, and keeps going while hundreds of programs read and write.
SQLite is a library. There is no server to start: the program that wants a database includes SQLite, and SQLite reads and writes one ordinary file on disk, such as bakery.db. Copy the file and you have copied the database, tables, data and all. Setting it up takes nothing, and the file goes wherever you send it. That is why SQLite sits inside nearly every phone, most web browsers and a great many desktop programs, usually without their users knowing.
Both are free, open source and mature: each has been developed for more than twenty-five years, each for a different job.
When to use which
Pick SQLite when the data belongs to one program. A mobile app's local data, a desktop tool's documents, automated tests that need a throwaway database, a small website on a single machine that writes now and then, a dataset you want to hand to a colleague as one file, a notebook where you explore data on your own laptop: SQLite fits all of these. Its own documentation sums up its place well: SQLite does not compete with client/server databases, it competes with fopen(), the function a program uses to open a plain file.
Pick PostgreSQL when several programs or people share the data, when many users write at the same moment, when you need accounts and permissions, or when the data must live on its own machine, backed up and copied to a standby. Most web applications with real traffic, and most databases a team shares, run on a server like PostgreSQL (or MySQL, SQL Server, Oracle). PostgreSQL also brings things SQLite has no equivalent for: real date, time and interval types, exact decimals, arrays, and extensions such as PostGIS for maps.
For a learner the choice matters less than it seems. The core of SQL, SELECT, WHERE, joins, grouping, subqueries, window functions, is the same in both. Learn that core, practise wherever it is convenient, and keep the differences below in mind.
Why the handbook writes PostgreSQL's SQL
Running SQLite in the page has real advantages. There is nothing to install and no account to create, your queries never leave your browser, and the bakery is small enough that every query answers at once.
But SQLite is forgiving. It runs queries that most other databases reject, and it often gives an answer where it should give an error. Compare a number with text and you get no rows, silently. Leave a column out of GROUP BY and it picks a value from some row. Put a value in double quotes and it may take it as text. Someone who practises only on SQLite picks up habits that fail on the first server they use.
So the terminal checks every query against PostgreSQL's grammar before SQLite sees it. A query that PostgreSQL would reject does not run: the terminal answers with PostgreSQL's own error line and a sentence on how to fix it, linked to the rule in The Grammar of SQL. It also accepts a few PostgreSQL forms that SQLite lacks and translates them for SQLite: ILIKE, LEFT(text, n), RIGHT(text, n) and the x::type cast. And LIKE is case-sensitive here, as in PostgreSQL.
On one point the terminal asks for more than PostgreSQL does: a number in quotes, as in price > '3', is refused, although PostgreSQL would read the quoted 3 as a number. The terminal wants numbers written as numbers, so the type shows in the query. The goal is SQL you can take to a PostgreSQL server as it is; the sections below say what to check when you do.
Types: declared or suggested
In PostgreSQL a column's type is a promise. A column declared integer holds whole numbers and nothing else: inserting 'abc' fails with invalid input syntax for type integer. The types are many and precise: integer and bigint, numeric for exact decimals, real and double precision for floating point, text and varchar(n), boolean, date, time, timestamp, interval and more.
In SQLite a declared type is closer to a preference, which SQLite calls type affinity. Every value carries its own type, one of five storage classes: NULL, INTEGER, REAL, TEXT and BLOB. A column declared INTEGER turns '12' into the number 12 when it can, but it keeps 'abc' as text, without complaint. Since version 3.37, a table created with the STRICT keyword checks types much as PostgreSQL does; ordinary tables, the bakery's included, do not.
Comparisons follow the same logic. In the bakery, orders.day is TEXT holding dates such as '2026-03-02'. Compare it with a number and PostgreSQL stops with an error. SQLite runs the query, finds that the text and the number are not equal, and returns no rows, with no warning. The terminal refuses it, as PostgreSQL would:
SELECT id, time
FROM orders
WHERE day = 20260302;
ERROR: operator does not exist: text = integer day is a text column and 20260302 is a number: write the value as text in single quotes, in the column's own format.Write the date as text, in the column's own format: day = '2026-03-02'. The full rule is in Grammar: types.
Text and numbers also sort differently. As text, '10' comes before '9', because the first characters are compared first. Dates written year-month-day escape this trap because every part has a fixed width, which is why the bakery can keep them as text: see why text order is calendar order.
LIKE, ILIKE and letter case
PostgreSQL's LIKE is case-sensitive: 'Almond croissant' LIKE '%Croissant%' is false. For a match that ignores case, PostgreSQL adds ILIKE. SQLite's LIKE ignores case, but only for the 26 letters of plain English: 'Cake' LIKE 'cake' is true, while 'CAFÉ' LIKE 'café' is false. SQLite has no ILIKE.
This terminal follows PostgreSQL. With a capital C, LIKE finds the plain croissant only:
SELECT name
FROM product
WHERE name LIKE '%Croissant%'
ORDER BY id;
| name |
|---|
| Croissant |
ILIKE finds the almond one too:
SELECT name
FROM product
WHERE name ILIKE '%croissant%'
ORDER BY id;
| name |
|---|
| Croissant |
| Almond croissant |
In plain SQLite the first query would already return both rows, and the second would be a syntax error. If a query has to ignore case on both databases, lower the text first: lower(name) LIKE '%croissant%' works everywhere. SQLite's GLOB, a case-sensitive pattern match with * and ?, exists in SQLite only. More in Case: LIKE and ILIKE and Grammar: LIKE and ILIKE.
Dates and times
PostgreSQL has real date and time types. A date column holds a calendar day, and '2026-02-30' is rejected because February has no 30th. You can add days to a date (day + 1 is the next day), subtract two dates to get a number of days, take a part out with EXTRACT(MONTH FROM day) or EXTRACT(DOW FROM day) (0 is Sunday), cut a timestamp down to its month with date_trunc('month', ts) and format it with to_char(day, 'YYYY-MM').
SQLite has no date type at all. A date is stored as text ('2026-03-02'), as a Julian day number (REAL) or as Unix time, the seconds since 1970 (INTEGER), and a family of functions reads any of the three: date(), time(), datetime(), julianday() and strftime(). So date('2026-03-02', '+1 day') gives '2026-03-03' and strftime('%w', day) gives the day of the week, 0 for Sunday. They are handy, but none of them exists in PostgreSQL. SQLite does not check that a date is real either: date('2026-02-30') comes back as '2026-03-02'.
The bakery stores day and time as text, as many SQLite databases do. The handbook works with them in ways both databases accept: it compares the text, which works because these dates sort in calendar order, and it takes a part out with substr or CAST. Here are the orders of the first three days:
SELECT day, COUNT(*) AS orders
FROM orders
WHERE day >= '2026-03-02'
AND day < '2026-03-05'
GROUP BY day
ORDER BY day;
| day | orders |
|---|---|
| 2026-03-02 | 13 |
| 2026-03-03 | 10 |
| 2026-03-04 | 10 |
SQLite's own date functions are refused, as PostgreSQL would refuse them:
SELECT day, strftime('%w', day) AS weekday
FROM orders;
ERROR: function strftime(unknown, text) does not exist strftime() is not standard SQL: use a comparison of the date text, as in d >= 'YYYY-MM-DD', or substr(d, 1, 7) for its year and month instead.The dates and times page covers this ground in full: comparing and filtering ranges, taking a date apart and Grammar: dates, substr and CAST. If you move such a database to PostgreSQL, declare those columns as date and time. Comparisons with a quoted date keep working, and PostgreSQL's date functions become available.
Joining text with ||
Both databases join text with ||, the standard operator. (+ is for numbers only, in both.)
SELECT name || ' (' || category || ')'
AS label
FROM product
WHERE price > 4
ORDER BY id;
| label |
|---|
| Sourdough loaf (bread) |
| Walnut bread (bread) |
| Raspberry tart (cake) |
The difference shows with two numbers. SQLite turns 2 || 3 into the text '23'; PostgreSQL refuses it, because || means nothing for two integers. The terminal follows PostgreSQL:
SELECT id || staff_id FROM orders;
ERROR: operator does not exist: integer || integer || joins text, and id and staff_id are both numbers: add them with +, or make one of them text first, as in CAST(id AS TEXT) || ...Make one side text first, as in CAST(id AS TEXT) || '-' || staff_id. One rule is the same on both: anything joined with NULL is NULL, so a missing value blanks the whole label. COALESCE (below) fills the gap. PostgreSQL's concat() skips NULLs instead, and SQLite has had concat() too since version 3.44. To join the values of a whole group, PostgreSQL has string_agg(name, ', '), where older SQLite only had group_concat; SQLite added string_agg in the same version. See Grammar: string_agg and SUM.
GROUP BY and the columns you may show
PostgreSQL's rule: in a grouped query, every column in SELECT is either listed in GROUP BY or wrapped in an aggregate such as MAX or COUNT. (Grouping by a table's primary key also covers that table's other columns.) Anything else is an error, because a group can hold several values for that column and PostgreSQL will not choose one for you.
SQLite allows these "bare" columns and picks a value itself. Ask for each category's dearest price with a product name beside it:
SELECT category, name, MAX(price)
FROM product
GROUP BY category;
ERROR: column "product.name" must appear in the GROUP BY clause or be used in an aggregate function Each row of the result is one group, and product.name can hold several values inside a group: add product.name to GROUP BY, or wrap it in an aggregate such as MAX(product.name).Plain SQLite runs this and answers Sourdough loaf, Raspberry tart and Cinnamon roll. With a single MAX or MIN, SQLite documents that bare columns come from the row that holds it, so the names are right this time. Add a second aggregate, or use AVG, and the name comes from some row of the group, and nothing tells you which. If two products shared the top price, you would see one of them and never know about the other.
PostgreSQL refuses the query, and so does the terminal. Show only what the grouping can answer:
SELECT category, MAX(price) AS top_price
FROM product
GROUP BY category
ORDER BY category;
| category | top_price |
|---|---|
| bread | 4.5 |
| cake | 4.1 |
| pastry | 2.6 |
To get the name as well, rank the rows with a window function, as in the window functions section below. The rule is set out in Grammar: grouped or aggregated, and the order the clauses run in is on the grammar page.
Single and double quotes
In standard SQL, PostgreSQL included, single quotes hold a value ('cake') and double quotes hold a name, of a table or a column ("category"). You rarely need double quotes: only for a name with a space or capitals in it, or one that is also a keyword. PostgreSQL turns names without quotes into lowercase, so Category and category are the same column, while "Category" in quotes names a different one.
SQLite accepts double quotes for names too, but when the quoted word matches no column, it quietly takes it as a text value. SQLite's own documentation lists this among its quirks, kept so that old programs keep working. So the query below runs in plain SQLite and finds the cakes. The trouble comes later: if a column named cake were ever added, the same query would quietly change meaning.
SELECT name
FROM product
WHERE category = "cake";
ERROR: column "cake" does not exist "cake" in double quotes is the name of a column, and there is none: a text value takes single quotes, 'cake'.Single quotes for the value, and it runs on both:
SELECT name
FROM product
WHERE category = 'cake' AND price > 3.5
ORDER BY id;
| name |
|---|
| Lemon tart |
| Cheesecake slice |
| Raspberry tart |
See Grammar: double quotes, which also covers SQLite's backticks and AS 'text'.
LIMIT and OFFSET
Both accept LIMIT n OFFSET m: skip m rows, then keep n. SQLite also takes LIMIT m, n, with the number to skip first, the reverse of what most readers expect. PostgreSQL rejects that form with LIMIT #,# syntax is not supported. PostgreSQL also accepts the SQL standard's longer form, OFFSET 3 ROWS FETCH FIRST 2 ROWS ONLY, which SQLite does not.
SELECT name, price
FROM product
ORDER BY price DESC, id
LIMIT 3, 2;
ERROR: LIMIT #,# syntax is not supported LIMIT 3, 2 is not standard SQL: write LIMIT 2 OFFSET 3 (the row count first, then how many rows to skip).SELECT name, price
FROM product
ORDER BY price DESC, id
LIMIT 2 OFFSET 3;
| name | price |
|---|---|
| Lemon tart | 3.9 |
| Rye loaf | 3.8 |
Without ORDER BY, neither database promises which rows come first, and the two may well disagree. The id above breaks ties between equal prices, so the result is the same everywhere. See Grammar: LIMIT and OFFSET and ties and LIMIT.
True and false
PostgreSQL has a boolean type with three values: true, false and NULL. A comparison such as price > 3 is a boolean, and WHERE, HAVING, ON and CASE WHEN each need one. SQLite has no boolean type: a comparison gives 1 or 0, and since version 3.23 the words TRUE and FALSE are other names for 1 and 0.
The same query shows the difference. Here the dear column reads 1 and 0; PostgreSQL would show true and false (psql prints them as t and f).
SELECT name, price, price > 3 AS dear
FROM product
WHERE category = 'bread'
ORDER BY id;
| name | price | dear |
|---|---|---|
| Baguette | 1.2 | 0 |
| Sourdough loaf | 4.5 | 1 |
| Rye loaf | 3.8 | 1 |
| Seeded rolls | 3.8 | 1 |
| Focaccia | 3.6 | 1 |
| Walnut bread | 4.2 | 1 |
Because SQLite treats a condition as a number, it lets you add conditions up: SUM(price > 3) counts the dear products. PostgreSQL has no sum of booleans, and the terminal refuses it. It refuses WHERE 1 for the same reason: 1 is a number, not a condition.
SELECT SUM(price > 3) FROM product;
ERROR: function sum(boolean) does not exist price > 3 is a condition (true or false), not a number: count where it holds with SUM(CASE WHEN price > 3 THEN 1 ELSE 0 END).Count with a condition instead. FILTER after an aggregate works in both (SQLite since 3.30), and SUM(CASE WHEN price > 3 THEN 1 ELSE 0 END) works everywhere:
SELECT COUNT(*) FILTER (WHERE price > 3)
AS dear,
COUNT(*) AS products
FROM product;
| dear | products |
|---|---|
| 9 | 20 |
See Grammar: conditions and conditional counts and sums.
IFNULL, COALESCE and NULL
COALESCE(a, b, ...) returns the first of its arguments that is not NULL, in both databases. SQLite also has IFNULL(a, b), the two-argument version, which PostgreSQL does not have.
SELECT name, IFNULL(email, 'no email')
FROM customer;
ERROR: function ifnull(text, unknown) does not exist ifnull() is not standard SQL: use COALESCE(a, b) instead.SELECT name,
COALESCE(email, 'no email') AS email
FROM customer
WHERE town = 'Old Town'
ORDER BY id
LIMIT 4;
| name | |
|---|---|
| David Chen | no email |
| Farid Haddad | FARID.HADDAD@MAIL.COM |
| Wen Petrov | no email |
| Hassan Ivanova | hassan.ivanova@post.net |
NULLIF(a, b), which turns a value into NULL, exists in both. Two more NULL differences are worth knowing. In sorting, SQLite puts NULLs first in ascending order and PostgreSQL puts them last; when it matters, write NULLS FIRST or NULLS LAST, which both accept (SQLite since 3.30). And SQLite's x IS 3, an equality that treats two NULLs as equal, is written x IS NOT DISTINCT FROM 3 in standard SQL, a form both accept. See Grammar: COALESCE and IS and NULL in sorting and grouping.
IIF and CASE
SQLite 3.32 added IIF(condition, a, b), borrowed from Microsoft's databases. PostgreSQL has no IIF. CASE is in the SQL standard and in both, and it does everything IIF does:
SELECT name, IIF(price > 3, 'dear', 'cheap')
FROM product;
ERROR: function iif(boolean, unknown, unknown) does not exist iif() is not standard SQL: use CASE WHEN ... THEN ... ELSE ... END instead.SELECT name,
CASE WHEN price > 3 THEN 'dear'
ELSE 'cheap' END AS band
FROM product
WHERE category = 'cake'
ORDER BY id;
| name | band |
|---|---|
| Lemon tart | dear |
| Chocolate eclair | cheap |
| Carrot cake slice | dear |
| Cheesecake slice | dear |
| Raspberry tart | dear |
| Coffee eclair | cheap |
| Pistachio financier | cheap |
ROUND, division and decimals
SQLite's ROUND(x, n) rounds any number to n decimal places. PostgreSQL's two-argument round wants a numeric, its exact decimal type. Given a real or double precision value it fails with function round(double precision, integer) does not exist. The average of a real column is a double precision, so ROUND(AVG(price), 2), fine in SQLite, fails there. Convert first:
SELECT category,
ROUND(AVG(price)::numeric, 2)
AS avg_price
FROM product
GROUP BY category
ORDER BY category;
| category | avg_price |
|---|---|
| bread | 3.52 |
| cake | 3.29 |
| pastry | 1.9 |
The cast changes nothing in SQLite and lets the query run in PostgreSQL. CAST(AVG(price) AS NUMERIC) is the same thing, written out. Other pages of this handbook round without the cast, because their examples are written for the terminal; this is the first thing to add when you take one to a PostgreSQL server.
Division works alike in one respect: both divide two integers as integers, so 7 / 2 is 3. Write 7 / 2.0, or cast one side, to get 3.5. Division by zero differs: SQLite returns NULL, PostgreSQL stops with division by zero. Decimals differ too. In SQLite a number such as 0.1 is floating point, so 0.1 + 0.2 = 0.3 is false. In PostgreSQL the same literal is numeric, which is exact, and the comparison is true; with real values (0.1::real) it is floating point again. For money, use PostgreSQL's numeric; SQLite has no exact decimal type built in.
Changing data: RETURNING and upserts
This handbook only reads data, and its terminal will not run INSERT, UPDATE or DELETE. Two features for changing data are still worth knowing, because both databases now share them, with the same syntax.
RETURNING makes an INSERT, UPDATE or DELETE return the rows it touched. The common use is to get the id of a new row: INSERT INTO product (name, category, price) VALUES ('Fig roll', 'pastry', 2.3) RETURNING id. PostgreSQL has had it for many years; SQLite added it in version 3.35.
An upsert inserts a row, or updates it when its key already exists: INSERT INTO product (id, name, category, price) VALUES (1, 'Baguette', 'bread', 1.3) ON CONFLICT (id) DO UPDATE SET price = excluded.price. PostgreSQL has it since 9.5, SQLite since 3.24. SQLite's older INSERT OR REPLACE exists only in SQLite, and it behaves differently: it deletes the old row and inserts a new one, so any column you did not give falls back to its default. PostgreSQL also has MERGE, since version 15.
Window functions and CTEs: the same in both
Here the two agree. SQLite gained window functions in version 3.25 (2018) and common table expressions, WITH RECURSIVE included, in 3.8.3 (2014). PostgreSQL has had both since 8.4 (2009). ROW_NUMBER, RANK, LAG, PARTITION BY and WITH are written the same way in both. Older tutorials that say SQLite lacks them are out of date, as are those that say it has no RIGHT JOIN or FULL OUTER JOIN: both arrived in SQLite 3.39 (2022).
This is how to get the name of each category's dearest product, the question the bare column above could not answer safely. It runs unchanged on both:
WITH ranked AS (
SELECT category, name, price,
RANK() OVER (PARTITION BY category
ORDER BY price DESC)
AS r
FROM product
)
SELECT category, name, price
FROM ranked
WHERE r = 1
ORDER BY category, name;
| category | name | price |
|---|---|---|
| bread | Sourdough loaf | 4.5 |
| cake | Raspberry tart | 4.1 |
| pastry | Cinnamon roll | 2.6 |
Unlike the bare column, this query is honest about ties: two products sharing the top price would both get rank 1, and both would be listed. See numbering and ranking and WITH: naming a result.
Many users at once
SQLite lets any number of connections read at the same time, but only one can write at a time, and a writer locks the whole database file, not just the rows it changes. In WAL mode (write-ahead logging), readers carry on during a write, but writers still wait their turn. For one application, or a website that writes now and then, that is plenty. With many people writing at the same moment, they queue. SQLite also has no users: whoever can open the file can read and change everything in it, so access depends on the operating system's file permissions.
PostgreSQL was built for many users at once. Each connection is served by its own process. A scheme called multiversion concurrency control means that, in the words of its manual, reading never blocks writing and writing never blocks reading; two writers wait for each other only when they change the same row. It has roles with passwords, GRANT and REVOKE per table, and even rules per row (row-level security). It can keep copies of the database on standby servers. None of this shows in a SELECT, but it is a large part of why companies run a server.
Summary table
| Topic | PostgreSQL | SQLite |
|---|---|---|
| What it is | a server that programs connect to | a library that reads one file |
| Column types | enforced | a preference (affinity); STRICT tables enforce them |
| Text compared with a number | an error | runs, usually finds nothing |
LIKE | case-sensitive; ILIKE ignores case | ignores case for A to Z; no ILIKE |
| Dates | date, time, timestamp, interval; EXTRACT, to_char | text or numbers; date(), strftime() |
2 || 3 | an error | '23' |
Column not in GROUP BY | an error | a value from some row |
"cake" | a column name | text, when no column has that name |
LIMIT 3, 2 | an error; LIMIT 2 OFFSET 3 or FETCH FIRST | skips 3, keeps 2 |
| Booleans | a boolean type: true, false | 1 and 0 |
IFNULL, IIF | none: COALESCE, CASE | both, plus COALESCE and CASE |
ROUND(x, 2) on real | needs x::numeric | works |
| Division by zero | an error | NULL |
| NULLs in ascending order | last | first |
RETURNING, ON CONFLICT | yes | yes (3.35, 3.24) |
| Window functions, CTEs | yes | yes (3.25, 3.8.3) |
| Writers at the same time | many | one |
| Users and permissions | roles, GRANT, row-level security | none: the file's permissions |
Moving a query from one to the other
A query that runs in this terminal is written in PostgreSQL's grammar, so most of the work is done. Before running it on a PostgreSQL server, check three things. A ROUND(x, n) on a real value needs ::numeric. An ORDER BY on a column with NULLs puts them at the other end unless you say NULLS FIRST or NULLS LAST. And the server's tables may declare dates as date rather than text, which opens up EXTRACT and date arithmetic but rules out substr on them.
The other way round, from PostgreSQL to plain SQLite, look for ILIKE, the :: cast, LEFT and RIGHT, date functions such as EXTRACT and date_trunc, and any column type SQLite does not know, such as arrays or intervals. Then remember that plain SQLite will run some mistakes that PostgreSQL would have caught, which is the reason this handbook checks them for you.