Error "functions in index expression must be marked IMMUTABLE", not time stamp

Question

I'm trying to create the following index;

CREATE INDEX idx_concat_paostartno_paostartsuff ON 
    dmv_os_addbase_residential (concat(pao_start_number || pao_start_suffix));

I get the error;

ERROR: functions in index expression must be marked IMMUTABLE`

I think this is the mixing of types, as pao_start_number is type bigint and pao_start_suffix is varchar. I've tried to resolve as;

CREATE INDEX idx_concat_paostartno_paostartsuff ON  
  dmv_os_addbase_residential (concat((pao_start_number :: text) || pao_start_suffix))

but same error.

Am I right in identifying that cause of the error and how can I resolve it? I am using Postgres 9.6

Answer 1

You probably want

CREATE INDEX idx_concat_paostartno_paostartsuff
   ON dmv_os_addbase_residential ((pao_start_number || pao_start_suffix));

This index can only be used for queries like

SELECT *
FROM dmv_os_addbase_residential
WHERE pao_start_number || pao_start_suffix <operator> <constant>;

where <operator> is one of =, <, <=, > or >=.

Answer 2

Timezone aware timestamps are considered mutable by Postgres. So that is why it is failing. I found the following GIST which solved the problem for me: https://gist.github.com/cobusc/5875282

shortcut to GIST contents (thank you cobusc):

CREATE INDEX my_index_name_idx ON my_table (date(created_ts at TIME ZONE 'UTC'));

worked for me