SQL (SQLite & PostgreSQL)

SQL is the language for storing and querying data in relational databases. This section teaches SQL itself, then flags — on every page — exactly where SQLite and PostgreSQL disagree, because most real-world SQL pain comes from assuming one behaves like the other.

The relational model in five terms

A relational database stores data as a set of tables. That’s it — no nesting, no pointers, no objects.

Term	What it is
Table (relation)	A named, unordered set of rows with a fixed set of columns.
Row (tuple/record)	One fact. (3, 'Ursula K. Le Guin', 1929) is one author.
Column (attribute)	A named, typed slot present in every row.
Primary key	A column (or set of columns) that uniquely identifies a row.
Foreign key	A column holding a primary key value from another table — the link.

Two properties trip people up:

• Rows have no inherent order. A table is a set. If you want ordered output you must write ORDER BY. A query that looks sorted without one is a coincidence you should not rely on.
• Relationships are values, not links. There is no pointer from a book to its author. There is a books.author_id column holding the same number as authors.id. You reconstruct the relationship at query time with a join.

NULL is the third thing that trips people up. It means “no value here” — not zero, not empty string. It has its own three-valued logic, covered in querying.

SQL is declarative

You describe the result you want, not the steps to produce it. This is the single most important mental shift.

SELECT title FROM books WHERE year > 1970 ORDER BY title;

You did not say “open the table, scan every row, test the year, collect matches, sort them”. You said what the answer looks like. The database’s query planner decides whether to scan the table, use an index, sort in memory or on disk, and in what order to combine tables. That’s why:

• Adding an index can make a query 1000× faster without changing one character of the query.
• Two queries that look different can compile to the same plan.
• EXPLAIN exists — it is how you see the plan the engine chose.

The practical consequence: write SQL for clarity and correctness, then measure, then tune. Hand-optimising the text of a query is usually wasted effort.

The clause evaluation order

SQL is written in one order and evaluated in another. Knowing the real order explains most “why can’t I use that alias there?” errors.

FROM / JOIN     →  which rows exist, combined
WHERE           →  filter individual rows
GROUP BY        →  collapse rows into groups
HAVING          →  filter groups
SELECT          →  compute output expressions, assign aliases
DISTINCT        →  remove duplicate output rows
ORDER BY        →  sort
LIMIT / OFFSET  →  slice

Because SELECT runs after WHERE, an alias defined in SELECT is not visible in WHERE. It is visible in ORDER BY (which runs later) — in both SQLite and PostgreSQL.

-- Fails in PostgreSQL: "column \"price_with_tax\" does not exist"
SELECT price * 1.2 AS price_with_tax FROM books WHERE price_with_tax > 10;

-- Works in both engines
SELECT price * 1.2 AS price_with_tax FROM books ORDER BY price_with_tax DESC;

Caution

SQLite is lenient here and will often accept a SELECT alias in WHERE. PostgreSQL follows the standard and rejects it. Write the standard form so your SQL ports.

SQLite vs PostgreSQL: the shape of each

Both speak SQL. They are architecturally different products.

SQLite is a library, not a server. You link it into your process; it reads and writes one file on disk. There is no daemon, no port, no user accounts, no network protocol. sqlite3 app.db opens the file directly. The whole database — schema, data, indexes — is that single file, which you can copy, email, or commit.

PostgreSQL is a client-server database. A server process owns the data directory; clients connect over TCP or a Unix socket, authenticate, and send queries. Multiple machines can connect at once. It does full MVCC (multi-version concurrency control), so many writers work concurrently without blocking each other.

	SQLite	PostgreSQL
Deployment	Embedded library, one file	Server process + data directory
Concurrency	Many readers, one writer at a time	Many concurrent readers and writers (MVCC)
Typing	Dynamic (type affinity)	Static and strict
Types	5 storage classes	Rich: numeric, timestamptz, uuid, jsonb, arrays, enums, custom
Network access	None (it’s a file)	Built in, with roles and TLS
Setup cost	Zero	Install, configure, operate
Typical size ceiling	Comfortable to tens of GB	Terabytes

When to use which

Use SQLite for: application-local storage (desktop, mobile, CLI tools), test suites, caches, data analysis on a file you were handed, embedded devices, and read-heavy websites with a single application server. It is the most deployed database engine in the world for good reason — zero operations.

Use PostgreSQL for: anything with multiple concurrent writers, anything running on more than one application server, anything needing real types (money, timestamps with time zones, JSON with indexes), row-level security, replication, or a data set that will outgrow one machine’s page cache.

Tip

“Start with SQLite, move to Postgres when you need to” is a genuinely good default for new projects — if you write portable SQL. The comparison page has a migration checklist.

Connecting

SQLite

The sqlite3 CLI ships with most systems (apt install sqlite3, brew install sqlite). Point it at a file; it is created if it doesn’t exist.

sqlite3 bookstore.db

SQLite version 3.45.1
Enter ".help" for usage hints.
sqlite>

Turn on readable output first — the defaults are terse:

sqlite> .headers on
sqlite> .mode box

.mode box draws a Unicode table (SQLite 3.33+). Use .mode column on older builds. Commands starting with . are CLI commands, not SQL — they take no semicolon.

An in-memory throwaway database, useful for experiments:

sqlite3 :memory:

PostgreSQL

psql is the official client. A local server usually accepts:

psql -d bookstore                 # local socket, current OS user
psql -h localhost -U app -d bookstore   # TCP, prompts for password
psql "postgresql://app:secret@db.example.com:5432/bookstore"

psql (16.2)
Type "help" for help.

bookstore=#

Create the database first if it doesn’t exist:

createdb bookstore

In psql, commands starting with \ are client commands: \dt lists tables, \d books describes one, \q quits. Full cheat sheet on the comparison page.

Note

psql sends a statement when it sees a ;. Until then the prompt changes to bookstore-# to show you’re mid-statement. Same idea in sqlite3, which shows ...>.

Your first table and query

This runs unchanged in both engines.

schema.sql

CREATE TABLE authors (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  birth_year INTEGER
);

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title     TEXT NOT NULL,
  year      INTEGER,
  price     NUMERIC(6, 2)
);

INSERT INTO authors (id, name, birth_year) VALUES
  (1, 'Ursula K. Le Guin', 1929),
  (2, 'Ted Chiang',        1967),
  (3, 'Octavia E. Butler', 1947);

INSERT INTO books (id, author_id, title, year, price) VALUES
  (1, 1, 'The Dispossessed',       1974, 14.99),
  (2, 1, 'A Wizard of Earthsea',   1968, 11.50),
  (3, 2, 'Stories of Your Life',   2002, 16.00),
  (4, 3, 'Kindred',                1979, 13.25);

Load and query it:

sqlite3 bookstore.db < schema.sql
psql -d bookstore -f schema.sql

SELECT a.name, b.title, b.year
FROM books AS b
JOIN authors AS a ON a.id = b.author_id
WHERE b.year >= 1974
ORDER BY b.year;

       name        |        title         | year
-------------------+----------------------+------
 Ursula K. Le Guin | The Dispossessed     | 1974
 Octavia E. Butler | Kindred              | 1979
 Ted Chiang        | Stories of Your Life | 2002

Every page in this section builds on this schema.

Caution

INTEGER PRIMARY KEY means something special in SQLite: it becomes an alias for the internal rowid and auto-assigns values. In PostgreSQL the same text creates a plain integer key with no auto-numbering — you want GENERATED ALWAYS AS IDENTITY. See data types and DDL.

Section map

1. Overview — you are here.
2. Data types and DDL — CREATE TABLE, SQLite’s type affinity vs PostgreSQL’s real type system, constraints, ALTER TABLE.
3. Querying with SELECT — filtering, ordering, NULL logic, CASE, built-in functions.
4. Joins — inner/outer/cross, USING, self joins, set operations, anti-joins.
5. Aggregation and window functions — GROUP BY, HAVING, OVER (PARTITION BY ...), running totals.
6. Modifying data and transactions — INSERT/UPDATE/DELETE, upserts, RETURNING, ACID, isolation levels, savepoints.
7. Advanced SQL — subqueries, CTEs and recursion, views, indexes, EXPLAIN, JSON.
8. SQLite vs PostgreSQL — the full comparison, migration checklist, and CLI cheat sheets.

Key points

• A table is an unordered set of rows; use ORDER BY whenever order matters.
• SQL is declarative — you describe the result, the planner picks the strategy.
• Clauses evaluate FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, which explains alias scoping.
• SQLite is an embedded single-file library with dynamic typing and one writer at a time.
• PostgreSQL is a client-server engine with static types, rich data types, and MVCC concurrency.
• sqlite3 file.db and psql -d dbname are your entry points; . commands and \ commands are client features, not SQL.