Can a partial index on a jsonb column use ->> without casting in PostgreSQL 12?

Yes, PostgreSQL 12 allows it

PostgreSQL 12 permits a partial index on a jsonb column that uses the ->> operator without an explicit cast to text. Because ->> already returns text, the cast is unnecessary in this version.

Confirmed facts

• A partial index can be defined as CREATE INDEX ... ON t (j ->> 'color') WHERE ....
• The planner considers the index only when the query predicate matches the indexed expression exactly, such as WHERE j ->> 'color' = 'red'.
• Adding a cast in the query, for example (j ->> 'color')::text = 'red', makes the expression text different and may cause a sequential scan.
• The partial index predicate must be implied by the query's WHERE clause; otherwise the index is skipped for non-matching rows.

Verification steps

1. Create the partial index using the ->> expression and the row filter.
2. Run the query with the same ->> expression and no cast.
3. Execute EXPLAIN (ANALYZE, BUFFERS) and confirm that an Index Scan appears in the plan.
4. Run ANALYZE on the table if the planner still chooses a sequential scan.

CREATE INDEX idx_orders_status ON orders (info ->> 'status')
WHERE info ->> 'status' IS NOT NULL;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders
WHERE info ->> 'status' = 'shipped';

Assumptions and uncertainty

This assumes PostgreSQL 12 and a standard btree partial index. Index use depends on statistics and selectivity, so a matching expression does not guarantee an index scan. If the query uses a different JSON key, adds a cast, or contains a predicate that does not imply the index condition, the planner will likely ignore the index.