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

Question

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?

Take a look at concat_ws() which handles the NULL cases, too. — joop
– joop, Commented Jan 5, 2016 at 9:53
The recommended method (from the docs) is to use coalesce() inside to_tsvector() (and the tsvector concatenation with ||), f.ex.: to_tsvector('english',coalesce(title,'')) || to_tsvector('english',coalesce(description,'')) — pozs
– pozs, Commented Jan 5, 2016 at 10:19

jcaron · Accepted Answer · 2016-01-05 09:54:21Z

9

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.

answered Jan 5, 2016 at 9:54

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related