CODEWITHSUNDEEP
sqlpostgresqlindexingconcatenation
Christoffer
Christoffer

Reputation: 197

Can't use concat function in Postgres (9.3) index

I'm creating an index over several text columns (in Postgres 9.3) and I would like to use concat, e.g:

CREATE INDEX 
ON my_table
USING gin (to_tsvector('english', concat(title, ' ', description)))

However, when I try to do this I get the following error:

ERROR: functions in index expression must be marked IMMUTABLE

Vanilla concatenation with the || operator works fine. However, I'd prefer to use concat since description might be NULL, and the || operator seems to turn any concatenation with NULL into NULL.

If I understand this correctly this means that concat is not marked as immutable, which I don't understand.

Of course, I can just coalesce all the nullable columns, but it feels inelegant. More than anything, I'm curious as to why I can't use concat in my index?

Upvotes: 4

Views: 2836

Answers (1)

jcaron
jcaron

Reputation: 17720

The reason CONCAT is not IMMUTABLE was explained by Tom Lane in this post:

concat() invokes datatype output functions, which are not necessarily immutable. An easy example is that timestamptz_out's results depend on the TimeZone setting.

I.e. that's due to the fact that it will accept non-text input, which may change based on session settings when converted to text.

You'll probably have to roll your own function for this.

Upvotes: 9

Related Questions