In PostgreSQL, why does CREATE INDEX ON book_orders ((ordered_at::date)) fail when ordered_at is timestamptz, and how do you index the order day safely?
answer
- index entries must never change meaning
- the cast depends on a session setting
- the error message names the label
- pin the time zone explicitly
- partial-index predicates follow the same rule
basics
~10 sCasting timestamptz to date depends on the session's TimeZone, so it is STABLE, and index expressions must be IMMUTABLE. Index (ordered_at AT TIME ZONE 'UTC')::date instead, and query with exactly that expression.
solid answer
~50 sPostgreSQL computes an index expression when a row is inserted and trusts the stored result from then on, so `CREATE INDEX` rejects any function not marked `IMMUTABLE` with `functions in index expression must be marked IMMUTABLE` (SQLSTATE `42P17`). `date(timestamptz)` is `STABLE`: the same instant falls on different days under different `TimeZone` settings. Pin the zone: `(ordered_at AT TIME ZONE 'UTC')::date` uses only immutable functions, and queries must repeat that exact expression. Partial-index predicates follow the same rule, so `WHERE ordered_at > now() - interval '30 days'` fails with `functions in index predicate must be marked IMMUTABLE`. In PostgreSQL 18, a generated column you want to index must be declared `STORED`, because the default kind is virtual and cannot be indexed. Never wrap the cast in a function falsely labelled `IMMUTABLE`: the index then returns wrong rows silently.
code
sql · 15 linesCREATE TABLE book_orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
book_id bigint NOT NULL REFERENCES books,
ordered_at timestamptz NOT NULL DEFAULT now()
);
-- ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX book_orders_day_bad ON book_orders ((ordered_at::date));
-- works: the zone is pinned, so the expression is immutable
CREATE INDEX book_orders_day_idx
ON book_orders (((ordered_at AT TIME ZONE 'UTC')::date));
SELECT count(*) FROM book_orders
WHERE (ordered_at AT TIME ZONE 'UTC')::date = DATE '2026-10-01';go deeper
Recall that functions in an index expression or a partial-index predicate must be IMMUTABLE, and that the error message says exactly that.
Explain why timestamptz-to-date is STABLE, how pinning the zone fixes it, and why queries must repeat the indexed expression exactly.
Spot false IMMUTABLE labels, handle 17's safe search_path during maintenance, and choose between an expression index and a STORED generated column on 18.
Decide which business day the index encodes, UTC or a store's local zone, because every report built on that index inherits the choice.
## Why index expressions must be IMMUTABLE An **expression index** stores the result of an expression, computed when each row is inserted or updated, and the planner later looks those results up as if they were facts about the row. That is only correct if the expression gives the same answer for the same row forever. PostgreSQL enforces it with function **volatility labels**: every function used in an index expression must be `IMMUTABLE`, meaning its result depends only on its arguments. Otherwise `CREATE INDEX` stops with: - `functions in index expression must be marked IMMUTABLE`, for the indexed expression; - `functions in index predicate must be marked IMMUTABLE`, for a partial index's `WHERE` clause. Both carry SQLSTATE **`42P17`** (`invalid_object_definition`). ## The timestamptz-to-date trap A `timestamptz` value is an absolute instant. Which calendar day it belongs to depends on the session's `TimeZone` setting: an order placed at 23:30 UTC is on the next day in Tokyo. So `ordered_at::date`, the function `date(timestamptz)`, is labelled `STABLE`, constant within a statement but not across settings, and an index on it is refused. | Expression on `book_orders.ordered_at` (timestamptz) | Label | Indexable? | |---|---|---| | `ordered_at::date` | STABLE | No | | `to_char(ordered_at, 'YYYY-MM-DD')` | STABLE | No | | `(ordered_at AT TIME ZONE 'UTC')::date` | IMMUTABLE throughout | Yes | | `now()` in a predicate | STABLE | No | The fix pins the zone: `ordered_at AT TIME ZONE 'UTC'` turns the instant into a `timestamp` (without time zone) using an explicit zone name, which is immutable, and casting a `timestamp` to `date` is immutable too. Two consequences: 1. **The index encodes a business decision.** It answers "which UTC day"; a store reporting by local day needs that zone instead. 2. **Queries must repeat the expression exactly**, `WHERE (ordered_at AT TIME ZONE 'UTC')::date = DATE '2026-10-01'`. A query on `ordered_at::date` will not match it. ## Partial-index predicates follow the same rule A "last 30 days" partial index is a classic attempt: `WHERE ordered_at > now() - interval '30 days'`. It fails because `now()` is `STABLE`, and even if it were allowed the index would silently go stale as time moved on. Workable alternatives: - a fixed cutoff written as a literal, such as `WHERE ordered_at >= '2026-10-01 00:00+00'`, which is converted to a constant once when the statement is parsed, with the index recreated on a schedule; - a predicate on immutable column values, such as `WHERE status = 'pending'`; - no predicate at all, if the range query is selective enough on a plain index. ## Generated columns in PostgreSQL 18 An alternative to repeating the expression in every query is a generated column. In PostgreSQL 18, a plain `GENERATED ALWAYS AS (...)` column is **virtual** by default, computed on read, and `CREATE INDEX` on it fails with `indexes on virtual generated columns are not supported`. To index it, declare it **`STORED`**; adding a stored generated column to an existing table rewrites the table, so plan that step separately. The generation expression must itself be immutable, so the same zone-pinning applies. ## Lying about volatility, and the 17 search_path rule - **A false `IMMUTABLE` label is accepted.** PostgreSQL trusts the label, so wrapping `ordered_at::date` in a function marked `IMMUTABLE` lets `CREATE INDEX` succeed. Each entry is then computed under whatever `TimeZone` the inserting session had, and lookups from other sessions can miss or mismatch rows. Fixing the function later also requires a `REINDEX`. - **Safe search_path during maintenance (17+).** Since PostgreSQL 17, `CREATE INDEX`, `REINDEX`, `ANALYZE`, `VACUUM` and other maintenance commands run functions with a safe `search_path`. A user function used by an expression index that refers to objects in a non-default schema must schema-qualify them or set its own search path when it is created; otherwise a `REINDEX` that worked on 16 can fail after an upgrade. ## Checklist - Use only immutable functions, or pin the zone, in index expressions and predicates. - Repeat the indexed expression verbatim in queries. - On 18, write `STORED` for any generated column you plan to index. - Never mark a function `IMMUTABLE` unless it truly is. - Check the plan: `EXPLAIN` should show the index condition on the expression; a sequential scan usually means the query's expression differs from the indexed one.
- What goes wrong if you wrap ordered_at::date in a function and mark it IMMUTABLE?`CREATE INDEX` accepts it, because PostgreSQL trusts the label. Each entry is computed under the inserting session's `TimeZone`, so the same instant can be stored under different days, and a session in another zone computes keys that do not match. The index silently returns wrong or missing rows, and correcting the function also requires a `REINDEX`.
- Why can REINDEX of an expression index built on PostgreSQL 16 fail after upgrading to 17 or later?Since 17, maintenance commands such as `CREATE INDEX`, `REINDEX`, `ANALYZE` and `VACUUM` run functions with a safe `search_path`. A function in the index expression that refers to a table or function in a non-default schema without qualifying it can no longer resolve it. Schema-qualify those references, or give the function its own search path when creating it.
- ordered_at::date is IMMUTABLE because a cast always returns the same type.
- A partial index WHERE ordered_at > now() - interval '30 days' keeps a rolling window.
- Marking a wrapper function IMMUTABLE is a harmless way to satisfy CREATE INDEX.
- In PostgreSQL 18, any generated column can be indexed, stored or virtual.
- A query on ordered_at::date still uses an index on the UTC-pinned expression.
Assumes PostgreSQL 18, where generated columns are virtual unless STORED is written and cannot then be indexed; 17 added the safe search_path for maintenance commands.