Creating index on concatenated char columns fails is Postgres 9 (regression)

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Creating index on concatenated char columns fails is Postgres 9 (regression)
Date: 2014-10-02 20:49:34
Message-ID: 147A7F6B6CF545B9925669D8584DE18D@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Steps to reproduce:

Run commands

create temp table test (kuupaev date, kellaaeg char(5) ) on commit drop;
create index test on test ((kuupaev||kellaaeg));

in

"PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit"

Observed result:

ERROR: functions in index expression must be marked IMMUTABLE

In

"PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit"

those commands work OK.

I need this index to speed up query

SELECT
max( kuupaev||kellaaeg )
from ALGSA
where laonr=?nlaonr and kuupaev <=?prmLOPP and kuupaev||kellaaeg <= ?someparam

How to fix or other way to speed this query?

Posted also in http://stackoverflow.com/questions/26161561/how-to-create-composite-index-in-postgres-9

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2014-10-02 20:49:49 How to find greatest record before known values fast
Previous Message Jonathan Vanasco 2014-10-02 20:13:16 Benching Queries