Partial and expression indexes

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

A partial unique index allows only one active plan per user. What does the last SELECT print?

SQL · PostgreSQL 17

CREATE TABLE subs (user_id int, plan text, active bool);
CREATE UNIQUE INDEX one_active ON subs (user_id) WHERE active;
INSERT INTO subs VALUES
  (1, 'basic', false), (1, 'pro', false), (1, 'max', true);
SELECT count(*) FROM subs WHERE user_id = 1;

Options31A unique violation on the second row2

Hint 1 of 1

Which rows are in the index?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A 3

Correct. Inactive rows aren't in the index, so only the single active row is checked for uniqueness.

A partial index only holds rows that match its WHERE clause. The two inactive rows aren't in it, so uniqueness is checked only among active rows, and there is just one.

Why not the others?

B 1. Inactive duplicates are allowed; the index only covers active rows.
C A unique violation on the second row. The second row is inactive, so it's outside the partial index.
D 2. All three rows are inserted in one statement and none conflicts.

Result on PostgreSQL 17 (psql)

count
3

(1 row)

30-second answer

A partial index covers only rows matching a WHERE clause, such as WHERE deleted_at IS NULL or WHERE status = 'pending', so it stays small and fast when queries only ever look at that slice. An expression index stores a computed value, like lower(email), so a query that filters on exactly that expression can use it. A partial unique index enforces rules like 'one active subscription per user'.

Practise saying it · 30 s. No recording, just a timer

Explain partial and expression indexes.

Say your answer out loud, then check which of these points you covered:

Partial: only rows matching WHERE
Expression: computed value
Query must match
Partial unique for business rules

Deep dive

The query's WHERE clause must imply the index predicate for the planner to use a partial index.

Expression indexes need the query to use the same expression, and the function must be IMMUTABLE.

Case-insensitive uniqueness: CREATE UNIQUE INDEX ON users (lower(email)), or the citext type.

Queue tables: a partial index on pending jobs stays tiny even with millions of finished rows.

Case-insensitive unique emails

SQL · PostgreSQL 17

CREATE TABLE users (id int PRIMARY KEY, email text NOT NULL);
CREATE UNIQUE INDEX users_email_lower ON users (lower(email));
INSERT INTO users VALUES (1, 'Ana@Example.com');
INSERT INTO users VALUES (2, 'ana@example.com');

Common mistakes

Querying WHERE email = $1 against an index on lower(email).
Querying a partial index with a parameter: with a generic plan, the planner can't prove the value matches the index predicate, so it skips the index.
Soft-delete tables with full unique constraints that block re-creating a deleted record.

Senior take & follow-ups

Give one partial and one expression example and the 'query must match' caveat.

Follow-up questions

Think of your answer first, then open the hint.

How do you enforce one active row per user?
Show hint
CREATE UNIQUE INDEX ... (user_id) WHERE active.
Why must the function be IMMUTABLE?
Show hint
Its stored result must never change for the same input.

Suggest a fix if something here is wrong or out of date.