Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Postgres index creation IMMUTABLE error

0

Hello , Im trying to create an index and getting this error .Can't find any solution yet how to rewrite the index creation

msdp_central=> CREATE INDEX i_account_id_search ON t_subscriber USING btree (upper(unaccent((f_account_id)::text)) varchar_pattern_ops, f_unit_pk); ERROR: functions in index expression must be marked IMMUTABLE

Please help

Topics
Database
Tags
PostgreSQL
Language
English
asked 3 years ago1.1K views
1 Answer
0

Hi,

Investigated this a bit and according to https://www.postgresql.org/docs/current/sql-createindex.html:

All functions and operators used in an index definition must be “immutable”.

In your case unaccent is STABLE, not immutable.

Try removing it to see if index can be created. If you still need it, you may need to find/build an immutable version of that function.

Hope it helps

EXPERT
answered 3 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content