Create postgresql index on text column casted to array

Question

I have a postgresql table that has a column with data type = 'text' in which I need to create an index which involves this column being type casted to integer[]. However, whenever I try to do so, I get the following error:

ERROR: functions in index expression must be marked IMMUTABLE

Here is the code:

create table test (a integer[], b text);

insert into test values ('{10,20,30}','{40,50,60}');

CREATE INDEX index_test on test USING GIN ((   b::integer[]      ));

Note that one potential workaround is to create a function that is marked as IMMUTABLE that takes in a column value and performs the type casting within the function, but the problem (aside from adding overhead) is that I have many different 'target' array data types (EG: text[], int2[], int4[], etc...), and it would not be possible to create a separate function for each potential target array data type.

This was also asked on the mailing list, where it was answered. If you ask a question on both Stack Overflow and the mailing list, please say you have done so and link to the Stack Overflow post from the mailing list post and/or link to the list post in the archives from your stack overflow question. [Edit: Given the difference in post dates, probably a near-identical question by someone else. My bad.] — Craig Ringer
– Craig Ringer, Commented Dec 10, 2011 at 15:36

Craig Ringer · Accepted Answer · 2011-12-10 15:42:01Z

3

Answered in this thread on the PostgreSQL mailing lists. Click on "Follow-ups" or "next by thread" in the links after the post to follow the (short) thread on the topic.

There's no recipe given there, but Tom's just talking about defining an explicit cast from text[] to integer[]. If time permits I'll flesh this answer out with an example.

answered Dec 10, 2011 at 15:42

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create postgresql index on text column casted to array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related