Selecting and filtering

Reading is the most common thing you do with a database, and SELECT is how you do it. A SELECT describes which columns you want, which rows qualify, in what order, and how many. The server returns a result set — zero or more rows — without changing anything on disk.

Every output below is real, from PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop on a Mac, September 2026), run in one empty database, top to bottom. It starts with the course’s canonical dataset, so the rows you see here are the rows you get from the insert lesson. Timings are from this Mac.

-- sql/setup.sql
CREATE TABLE authors (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  born int
);
CREATE TABLE books (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  author_id   bigint REFERENCES authors (id),
  published   int,
  copies_sold int NOT NULL DEFAULT 0
);
CREATE TABLE sales (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint REFERENCES books (id),
  region  text NOT NULL,
  copies  int NOT NULL
);
INSERT INTO authors (name, born) VALUES ('Mara Linde', 1968), ('Owen Pryce', 1975), ('Sela Vance', 1990);
INSERT INTO books (title, author_id, published, copies_sold) VALUES
  ('Quiet Harbors', 1, 2003, 450), ('The Glass Orchard', 1, 2018, 1250), ('Tidal Notes', 2, 2011, 600), ('Unlinked Draft', NULL, 2020, 0);
INSERT INTO sales (book_id, region, copies) VALUES
  (2, 'East', 900), (3, 'West', 400), (1, 'West', 400), (3, 'East', 200), (2, 'West', 150), (1, 'East', 50);

Choosing columns

List the columns you want after SELECT. Use * only for quick exploration; in real code, name the columns so the result stays stable even if someone adds a column later.

psql

-- sql/select-columns.sql
SELECT id, name, born FROM authors;

Node.js

const res = await pool.query('SELECT id, name, born FROM authors');
console.log(res.rows);

Python

cur.execute("SELECT id, name, born FROM authors")
for row in cur.fetchall():
    print(row)

Go

rows, err := conn.Query(ctx, "SELECT id, name, born FROM authors")
defer rows.Close()

Rust

let rows = sqlx::query("SELECT id, name, born FROM authors")
    .fetch_all(&pool)
    .await?;

 id |    name    | born
----+------------+------
  1 | Mara Linde | 1968
  2 | Owen Pryce | 1975
  3 | Sela Vance | 1990
(3 rows)

SELECT * returns whatever columns the table has today:

-- sql/select-star.sql
SELECT * FROM books WHERE id = 1;

 id |     title     | author_id | published | copies_sold
----+---------------+-----------+-----------+-------------
  1 | Quiet Harbors |         1 |      2003 |         450
(1 row)

The drivers were each run once against this dataset (results collected at the end of the lesson). One detail visible there: node-postgres returns bigint ids as strings (id: '1'), because a JavaScript number cannot hold every 64-bit integer.

Narrowing with WHERE

WHERE keeps only the rows whose condition is true. You can compare with =, <, >, <=, >=, and combine conditions with AND and OR.

psql

-- sql/select-where.sql
SELECT name, born
FROM authors
WHERE born >= 1970 AND born < 1995;

Node.js

const res = await pool.query(
  'SELECT name, born FROM authors WHERE born >= $1 AND born < $2',
  [1970, 1995],
);

Python

cur.execute(
    "SELECT name, born FROM authors WHERE born >= %s AND born < %s",
    (1970, 1995),
)

Go

rows, err := conn.Query(ctx,
    "SELECT name, born FROM authors WHERE born >= $1 AND born < $2",
    1970, 1995)

Rust

let rows = sqlx::query("SELECT name, born FROM authors WHERE born >= $1 AND born < $2")
    .bind(1970_i32)
    .bind(1995_i32)
    .fetch_all(&pool)
    .await?;

    name    | born
------------+------
 Owen Pryce | 1975
 Sela Vance | 1990
(2 rows)

IN, BETWEEN, and pattern matching

IN checks membership in a list, BETWEEN is an inclusive range (both ends are included: 1968 and 1975 below), and LIKE matches text patterns.

-- sql/select-between-in.sql
SELECT name, born
FROM authors
WHERE born BETWEEN 1968 AND 1975;

SELECT title FROM books WHERE published IN (2003, 2011);

    name    | born
------------+------
 Mara Linde | 1968
 Owen Pryce | 1975
(2 rows)

     title
---------------
 Quiet Harbors
 Tidal Notes
(2 rows)

LIKE is case-sensitive; PostgreSQL also offers ILIKE for a case-insensitive match. In patterns, % matches any run of characters and _ matches exactly one.

psql

-- sql/select-ilike.sql
SELECT name, born
FROM authors
WHERE born BETWEEN 1960 AND 1980
  AND name ILIKE '%a%';

Node.js

const res = await pool.query(
  'SELECT name, born FROM authors WHERE born BETWEEN $1 AND $2 AND name ILIKE $3',
  [1960, 1980, '%a%'],
);

Python

cur.execute(
    "SELECT name, born FROM authors WHERE born BETWEEN %s AND %s AND name ILIKE %s",
    (1960, 1980, "%a%"),
)

Go

rows, err := conn.Query(ctx,
    "SELECT name, born FROM authors WHERE born BETWEEN $1 AND $2 AND name ILIKE $3",
    1960, 1980, "%a%")

Rust

let rows = sqlx::query(
    "SELECT name, born FROM authors WHERE born BETWEEN $1 AND $2 AND name ILIKE $3",
)
.bind(1960_i32)
.bind(1980_i32)
.bind("%a%")
.fetch_all(&pool)
.await?;

    name    | born
------------+------
 Mara Linde | 1968
(1 row)

The case rules, side by side ('m%' is lowercase, the name starts with M):

-- sql/select-like-case.sql
SELECT name,
       name LIKE 'm%'  AS like_lower_m,
       name ILIKE 'm%' AS ilike_lower_m,
       name LIKE '_ara%' AS underscore_matches_one_char
FROM authors WHERE id = 1;

    name    | like_lower_m | ilike_lower_m | underscore_matches_one_char
------------+--------------+---------------+-----------------------------
 Mara Linde | f            | t             | t
(1 row)

Handling NULL

NULL means “unknown”, so it does not behave like an ordinary value. You cannot test it with =; use IS NULL and IS NOT NULL instead. Unlinked Draft in the dataset has no author.

psql

-- sql/select-is-null.sql
SELECT title FROM books WHERE author_id IS NULL;

Node.js

const res = await pool.query('SELECT title FROM books WHERE author_id IS NULL');

Python

cur.execute("SELECT title FROM books WHERE author_id IS NULL")

Go

rows, err := conn.Query(ctx, "SELECT title FROM books WHERE author_id IS NULL")

Rust

let rows = sqlx::query("SELECT title FROM books WHERE author_id IS NULL")
    .fetch_all(&pool)
    .await?;

     title
----------------
 Unlinked Draft
(1 row)

The rest of the three-valued logic, run on the same table. = NULL returns nothing. <> 1 also drops the row with a NULL author, because unknown <> 1 is unknown, not true; IS DISTINCT FROM treats NULL as an ordinary value. And count(column) skips NULLs while count(*) does not:

-- sql/select-null.sql
SELECT title FROM books WHERE author_id = NULL;
SELECT title FROM books WHERE author_id <> 1 ORDER BY id;
SELECT title FROM books WHERE author_id IS DISTINCT FROM 1 ORDER BY id;
SELECT count(*) AS all_rows, count(author_id) AS non_null_author_id FROM books;

 title
-------
(0 rows)

    title
-------------
 Tidal Notes
(1 row)

     title
----------------
 Tidal Notes
 Unlinked Draft
(2 rows)

 all_rows | non_null_author_id
----------+--------------------
        4 |                  3
(1 row)

NULLs also have a sort position: last in ascending order by default, first with NULLS FIRST:

-- sql/select-null-order.sql
SELECT title, author_id FROM books ORDER BY author_id;
SELECT title, author_id FROM books ORDER BY author_id NULLS FIRST, id;

       title       | author_id
-------------------+-----------
 Quiet Harbors     |         1
 The Glass Orchard |         1
 Tidal Notes       |         2
 Unlinked Draft    |
(4 rows)

       title       | author_id
-------------------+-----------
 Unlinked Draft    |
 Quiet Harbors     |         1
 The Glass Orchard |         1
 Tidal Notes       |         2
(4 rows)

Sorting and paging

ORDER BY sorts the result; add DESC for descending. LIMIT caps the number of rows, and OFFSET skips rows from the top — together they let you page through a large table.

psql

-- sql/select-order-limit.sql
SELECT name, born
FROM authors
ORDER BY born DESC
LIMIT 2 OFFSET 1;

Node.js

const res = await pool.query(
  'SELECT name, born FROM authors ORDER BY born DESC LIMIT $1 OFFSET $2',
  [2, 1],
);

Python

cur.execute(
    "SELECT name, born FROM authors ORDER BY born DESC LIMIT %s OFFSET %s",
    (2, 1),
)

Go

rows, err := conn.Query(ctx,
    "SELECT name, born FROM authors ORDER BY born DESC LIMIT $1 OFFSET $2",
    2, 1)

Rust

let rows = sqlx::query("SELECT name, born FROM authors ORDER BY born DESC LIMIT $1 OFFSET $2")
    .bind(2_i64)
    .bind(1_i64)
    .fetch_all(&pool)
    .await?;

    name    | born
------------+------
 Owen Pryce | 1975
 Mara Linde | 1968
(2 rows)

In pgAdmin: run any of these in the Query Tool; results appear in the Data Output grid below, where you can also sort columns by clicking their headers.

Sort order is only deterministic if the sort key is unique. sales has a tie (two rows with 400 copies); add a tiebreaker such as the primary key so the order is reproducible:

-- sql/select-order-tiebreaker.sql
SELECT id, region, copies FROM sales ORDER BY copies DESC, id LIMIT 3;

 id | region | copies
----+--------+--------
  1 | East   |    900
  2 | West   |    400
  3 | West   |    400
(3 rows)

Text sorts by the database’s collation, not by byte value. In en_US.utf8, apple and Apple sit together; in "C", every uppercase letter sorts before every lowercase one:

-- sql/select-collation.sql
SELECT datcollate AS database_collation FROM pg_database WHERE datname = current_database();
SELECT w AS default_collation FROM (VALUES ('apple'), ('Banana'), ('cherry'), ('Apple')) t(w) ORDER BY w;
SELECT w AS c_collation FROM (VALUES ('apple'), ('Banana'), ('cherry'), ('Apple')) t(w) ORDER BY w COLLATE "C";

 database_collation
--------------------
 en_US.utf8
(1 row)

 default_collation
-------------------
 apple
 Apple
 Banana
 cherry
(4 rows)

 c_collation
-------------
 Apple
 Banana
 apple
 cherry
(4 rows)

And LIMIT without ORDER BY returns some rows, in whatever order the server happens to read them. It looked stable until one row was updated (PostgreSQL writes the new version of a row at a new place), and then the first two rows changed:

-- sql/limit-without-order.sql
SELECT id, title FROM books LIMIT 2;
UPDATE books SET copies_sold = copies_sold WHERE id = 1;
SELECT id, title FROM books LIMIT 2;
SELECT id, title FROM books ORDER BY id LIMIT 2;

 id |       title
----+-------------------
  1 | Quiet Harbors
  2 | The Glass Orchard
(2 rows)

UPDATE 1
 id |       title
----+-------------------
  2 | The Glass Orchard
  3 | Tidal Notes
(2 rows)

 id |       title
----+-------------------
  1 | Quiet Harbors
  2 | The Glass Orchard
(2 rows)

Deep OFFSET is slow, keyset paging is not

OFFSET n cannot skip rows without reading them: the server produces the first n + limit rows and throws n away. The cost grows with the page number. Keyset (“seek”) paging asks for rows after the last key you saw instead. On 200,000 rows, page 15,001 both ways (the plan shows the rows actually read):

-- sql/offset-vs-keyset.sql
CREATE TABLE events (id int PRIMARY KEY, payload text);
INSERT INTO events SELECT g, md5(g::text) FROM generate_series(1, 200000) g;
ANALYZE events;
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, SUMMARY ON)
SELECT id FROM events ORDER BY id LIMIT 10 OFFSET 150000;
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, SUMMARY ON)
SELECT id FROM events WHERE id > 150000 ORDER BY id LIMIT 10;

CREATE TABLE
INSERT 0 200000
ANALYZE
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Limit (actual time=19.105..19.108 rows=10.00 loops=1)
   ->  Index Only Scan using events_pkey on events (actual time=0.012..15.379 rows=150010.00 loops=1)
         Heap Fetches: 150010
         Index Searches: 1
 Planning Time: 0.095 ms
 Execution Time: 19.118 ms
(6 rows)

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit (actual time=0.004..0.006 rows=10.00 loops=1)
   ->  Index Only Scan using events_pkey on events (actual time=0.004..0.005 rows=10.00 loops=1)
         Index Cond: (id > 150000)
         Heap Fetches: 10
         Index Searches: 1
 Planning Time: 0.039 ms
 Execution Time: 0.010 ms
(7 rows)

OFFSET 150000 read 150,010 rows and took about 19 ms; the keyset form read 10 rows and took a few microseconds. Deep pages are rarely needed by real users but constantly needed by crawlers and export jobs. The trade-off: keyset needs a unique, indexed sort key (ORDER BY id, or (created_at, id)) and cannot jump straight to “page 300”.

Pattern matching and indexes

The advice that ILIKE '%text%' “cannot use a plain index” is true, and the fix is a trigram index. On the same 200,000 rows, four hits out of 200,000 for an ILIKE on '%A3F9C%' (the data is lowercase hex, which is why ILIKE matters): even with a plain B-tree on the column, PostgreSQL reads the whole table; with a pg_trgm GIN index it reads five heap blocks:

-- sql/ilike-trigram.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX events_payload_btree ON events (payload);
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, SUMMARY ON)
SELECT id FROM events WHERE payload ILIKE '%A3F9C%';
CREATE INDEX events_payload_trgm ON events USING gin (payload gin_trgm_ops);
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, SUMMARY ON)
SELECT id FROM events WHERE payload ILIKE '%A3F9C%';

CREATE EXTENSION
CREATE INDEX
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on events (actual time=11.414..102.843 rows=4.00 loops=1)
   Filter: (payload ~~* '%A3F9C%'::text)
   Rows Removed by Filter: 199996
 Planning Time: 0.296 ms
 Execution Time: 102.865 ms
(5 rows)

CREATE INDEX
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on events (actual time=0.157..0.167 rows=4.00 loops=1)
   Recheck Cond: (payload ~~* '%A3F9C%'::text)
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=5
   ->  Bitmap Index Scan on events_payload_trgm (actual time=0.147..0.147 rows=5.00 loops=1)
         Index Cond: (payload ~~* '%A3F9C%'::text)
         Index Searches: 1
 Planning Time: 0.188 ms
 Execution Time: 0.185 ms
(9 rows)

The same idea for WHERE lower(email) = ...: a plain index on payload is ignored, an expression index on the same expression is used:

-- sql/lower-expression-index.sql
EXPLAIN (COSTS OFF) SELECT id FROM events WHERE lower(payload) = 'abc';
CREATE INDEX events_payload_lower ON events (lower(payload));
EXPLAIN (COSTS OFF) SELECT id FROM events WHERE lower(payload) = 'abc';

                   QUERY PLAN
------------------------------------------------
 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on events
         Filter: (lower(payload) = 'abc'::text)
(4 rows)

CREATE INDEX
                     QUERY PLAN
----------------------------------------------------
 Bitmap Heap Scan on events
   Recheck Cond: (lower(payload) = 'abc'::text)
   ->  Bitmap Index Scan on events_payload_lower
         Index Cond: (lower(payload) = 'abc'::text)
(4 rows)

The indexes module goes deeper into all of this; the point here is that the query shape decides whether an index can help.

Reading AI-generated code

The driver results promised earlier:

$ (each snippet run once against the course dataset in a scratch database; the program then prints the values of one column)
[Choosing columns] Node.js (pg 8.23.0):
    [
      { id: '1', name: 'Mara Linde', born: 1968 },
      { id: '2', name: 'Owen Pryce', born: 1975 },
      { id: '3', name: 'Sela Vance', born: 1990 }
    ]
[Choosing columns] Python (psycopg 3.3.6):
    (1, 'Mara Linde', 1968)
    (2, 'Owen Pryce', 1975)
    (3, 'Sela Vance', 1990)
[Choosing columns] Go (pgx v5.11.0): Mara Linde, Owen Pryce, Sela Vance
[Choosing columns] Rust (sqlx 0.9.0): Mara Linde, Owen Pryce, Sela Vance
[WHERE] Node.js (pg 8.23.0): Owen Pryce, Sela Vance
[WHERE] Python (psycopg 3.3.6): Owen Pryce, Sela Vance
[WHERE] Go (pgx v5.11.0): Owen Pryce, Sela Vance
[WHERE] Rust (sqlx 0.9.0): Owen Pryce, Sela Vance
[BETWEEN + ILIKE] Node.js (pg 8.23.0): Mara Linde
[BETWEEN + ILIKE] Python (psycopg 3.3.6): Mara Linde
[BETWEEN + ILIKE] Go (pgx v5.11.0): Mara Linde
[BETWEEN + ILIKE] Rust (sqlx 0.9.0): Mara Linde
[IS NULL] Node.js (pg 8.23.0): Unlinked Draft
[IS NULL] Python (psycopg 3.3.6): Unlinked Draft
[IS NULL] Go (pgx v5.11.0): Unlinked Draft
[IS NULL] Rust (sqlx 0.9.0): Unlinked Draft
[ORDER BY / LIMIT / OFFSET] Node.js (pg 8.23.0): Owen Pryce, Mara Linde
[ORDER BY / LIMIT / OFFSET] Python (psycopg 3.3.6): Owen Pryce, Mara Linde
[ORDER BY / LIMIT / OFFSET] Go (pgx v5.11.0): Owen Pryce, Mara Linde
[ORDER BY / LIMIT / OFFSET] Rust (sqlx 0.9.0): Owen Pryce, Mara Linde

An assistant is asked for a paginated books endpoint and writes the obvious query:

🐛 Spot the bug

-- GET /api/books?page=N   (AI comment: "simple pagination, 2 per page")
SELECT id, title FROM books LIMIT 2 OFFSET :page_offset;

Show the answer

It passes every test on a static table. Without an ORDER BY, the server is free to return the rows in any order, and the order really does change when data is modified (shown above). Here a user reads page 1 of a four-row feed, someone edits row 1, and the user asks for page 2:

-- sql/pagination-bug.sql
-- page 1 of a "newest first" list, 2 per page, but no ORDER BY
CREATE TABLE feed (id int PRIMARY KEY, title text);
INSERT INTO feed VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
SELECT id, title FROM feed LIMIT 2 OFFSET 0;
-- someone edits row 1 while the user reads page 1 ...
UPDATE feed SET title = 'one (edited)' WHERE id = 1;
-- ... page 2:
SELECT id, title FROM feed LIMIT 2 OFFSET 2;

CREATE TABLE
INSERT 0 4
 id | title
----+-------
  1 | one
  2 | two
(2 rows)

UPDATE 1
 id |    title
----+--------------
  4 | four
  1 | one (edited)
(2 rows)

Page 2 shows row 1 again (edited) and never shows row 3. Both are silent: no error, no warning. The fix is a deterministic sort on a unique key:

-- sql/pagination-fix.sql
SELECT id, title FROM feed ORDER BY id LIMIT 2 OFFSET 0;
SELECT id, title FROM feed ORDER BY id LIMIT 2 OFFSET 2;

 id |    title
----+--------------
  1 | one (edited)
  2 | two
(2 rows)

 id | title
----+-------
  3 | three
  4 | four
(2 rows)

Mental Model

A SELECT is a pipeline: FROM (which rows exist) then WHERE (which qualify) then the select list then ORDER BY then LIMIT/OFFSET. Each stage only sees what the previous stage gave it. That is why NULL comparisons silently drop rows (the WHERE stage only keeps true), why an unordered LIMIT is arbitrary (no order was ever defined), and why a deep OFFSET is expensive (the earlier stages had to produce those rows before OFFSET could discard them).

Tips / gotchas

• NULL uses three-valued logic: a comparison against NULL is neither true nor false but unknown, so such rows are excluded by WHERE. Always use IS NULL (or IS DISTINCT FROM) rather than = NULL.
• LIMIT without an ORDER BY returns an arbitrary subset — the server may return rows in any order, and that order can change between runs. Always pair LIMIT with a deterministic ORDER BY, with a unique tiebreaker.
• ILIKE '%x%' cannot use a plain B-tree; a pg_trgm GIN index makes it fast (about 100 ms down to 0.2 ms above). lower(col) = ... needs an expression index.
• Prefer keyset paging over deep OFFSET for large tables.
• Selecting only the columns you need keeps result sets small and your code resilient to schema changes.

🐘 Order before you page

Pagination only makes sense on a stable sort. If two rows tie on your sort column, add a tiebreaker such as ORDER BY born DESC, id so the same row never appears on two pages.

Trade-offs

Option	Benefit	Cost
Explicit column list	Stable API, less data over the wire, no leaking of sensitive columns	Query needs an edit when the needed columns change
SELECT *	Fast to write, convenient for prototyping or exploring data	Breaks silently when someone adds/removes/renames a column later, and can leak columns that should not be exposed
Filter with WHERE in SQL	Pushed down to the database, can use an index, only the needed rows cross the wire	Requires understanding SQL and planning indexes that match the filter pattern
Fetch everything and filter in application code	Easy application-side logic, no complex SQL to write	Wastes bandwidth, misses the chance to use an index, and gets slower as the table grows
LIMIT/OFFSET paging	Simple, allows jumping to any page	Cost grows with the offset (19 ms at 150,000 rows)
Keyset paging	Constant cost per page	Needs a unique indexed sort key, no random page jumps

Common Misconceptions

• “WHERE col <> 1 returns every row that is not 1.” Not the rows where col is NULL (proved above).
• “LIMIT 10 gives me the first 10 rows.” It gives 10 rows. Which ones is unspecified without ORDER BY.
• “BETWEEN excludes the upper bound.” It includes both ends.
• “ILIKE is just a slower LIKE.” It also cannot use the same indexes; a trigram index makes both fast.
• “Sorting is the same everywhere.” It follows the collation: apple, Apple, Banana in en_US.utf8, Apple, Banana, apple in "C".

How do you correctly find rows where the born column is unknown?

WHERE born = NULL WHERE born IS NULL WHERE born == NULL WHERE born <> 0

What is true about LIMIT without an ORDER BY?

It always returns the oldest rows It returns an arbitrary, possibly changing subset It raises an error It sorts by primary key automatically

Which operator does a case-insensitive pattern match in PostgreSQL?

LIKE ILIKE BETWEEN IN

What does OFFSET 1 do in a SELECT?

Returns only the first row Skips the first row of the ordered result Limits the result to one row Offsets the column positions

Rows: books.author_id is 1, 1, 2, NULL. What does WHERE author_id <> 1 return?

Only the row with author_id 2 The rows with author_id 2 and NULL All four rows An error, because NULL cannot be compared

Submit

Mark as complete