[Solved] You attempt to create this partial index:CREATE INDEX idx_high_salary ON employees (salary) WHERE salary > 50000;But PostgreSQL returns an error. What is the most probable reason? | PostgreSQL

PostgreSQL - Indexing Strategies

You attempt to create this partial index:
CREATE INDEX idx_high_salary ON employees (salary) WHERE salary > 50000;
But PostgreSQL returns an error. What is the most probable reason?

AThe <code>WHERE</code> clause references the indexed column, which is not allowed

BThe index name conflicts with an existing table name

CThe <code>WHERE</code> clause uses a non-immutable function or expression

DPartial indexes cannot use comparison operators in the <code>WHERE</code> clause

Step-by-Step Solution

Solution:

Step 1: Understand partial index restrictions
PostgreSQL requires the WHERE clause to be immutable and deterministic.
Step 2: Analyze the expression
Using a constant comparison like salary > 50000 is allowed, but if the expression involved non-immutable functions, it would cause errors.
Step 3: Consider error causes
If the error occurs, it is likely due to a non-immutable function or expression in the WHERE clause.
Final Answer:
The WHERE clause uses a non-immutable function or expression -> Option C
Quick Check:
Partial index WHERE clause must be immutable [OK]

Quick Trick: WHERE clause must be immutable and deterministic [OK]

Common Mistakes:

Assuming indexed column cannot appear in WHERE clause
Ignoring immutability requirement
Confusing index name conflicts with errors

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

You attempt to create this partial index:

Step 1: Understand partial index restrictions

Step 2: Analyze the expression

Step 3: Consider error causes

Final Answer:

Quick Check:

Want More Practice?