Incident brief
Index expression must be immutable
An expression index may only use IMMUTABLE functions, because a changing result would silently corrupt the index; a non-immutable function in the expression raises this error.
What lands in your log
ERROR: functions in index expression must be marked IMMUTABLE
In 10 seconds
- What triggers it
- Create an index whose expression calls a STABLE or VOLATILE function (for example a timezone- or now()-dependent expression).
- Fix
- Index only IMMUTABLE expressions (or a plain column).
- Proof
- Reproduced on PostgreSQL 18.4 → A CREATE INDEX reproduces SQLSTATE 42P17; the non-immutable expression is rejected before the index is built.
Fix
What to do right now
Application-level steps for this error.
- Index only IMMUTABLE expressions (or a plain column).
- If a function is genuinely immutable but not marked so, mark it (or a wrapper) IMMUTABLE, only when the output truly never changes for the same input.
CREATE INDEX idx_ref_demo_lower
ON ref_demo ((lower(label)));
SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_ref_demo_lower';For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Every function used in an index expression/predicate must be IMMUTABLE. Inspect function volatility rather than guessing from its name.
Volatility of functions in the expression
Replace the function name; provolatile i/s/v means immutable/stable/volatile.
SELECT n.nspname AS schema_name, p.proname,
pg_get_function_identity_arguments(p.oid) AS arguments,
p.provolatile
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = '<function_name>'
ORDER BY n.nspname, arguments;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 18 Documentation, Appendix A. PostgreSQL Error Codes (Table A.1, Class 42, Syntax Error or Access Rule Violation)
42P17 → invalid_object_definitionRead the full section on postgresql.org →
Indexing a non-immutable expression
Index expressions must be reproducible, so PostgreSQL raises 'functions in index expression must be marked IMMUTABLE' when the expression is not.The session continues normally
The IMMUTABLE check on the index expression failed outside a transaction block, so nothing lingers before the next statement.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Create an index whose expression calls a STABLE or VOLATILE function (for example a timezone- or now()-dependent expression).
- 2PostgreSQL checks that every function in the index expression is IMMUTABLE.
- 3A non-immutable function aborts the CREATE INDEX with SQLSTATE 42P17.
An index on a computed expression failed because the expression depended on a non-immutable function, which would make the index inconsistent over time.
CREATE TABLE ref_demo(label text);
INSERT INTO ref_demo VALUES ('a');CREATE INDEX idx_bad ON ref_demo ((label || clock_timestamp()::text));SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
CREATE TABLE
INSERT 0 1ERROR: functions in index expression must be marked IMMUTABLE session_after_error
---------------------
ok
(1 row)The index builds once its expression is immutable.
Without this
Before: a non-immutable expression aborts
With this, tested
After: a plain-column index builds
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
The runbook for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Fix it — runbooks
Related errors
Verification
- Last verified
- 2026-07-24 (isolated lab, PostgreSQL 18.4)
- Verification scope
- Verified against PostgreSQL 18.4 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.