Re: [GENERAL] Issue with creation of Partial_indexes (Immutable?)

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:
> On Mon, 2008-08-25 at 12:23 +0800, Ow Mun Heng wrote:
>> CREATE INDEX idx_d_trh_code_id_partial
>> ON xmms.d_trh_table
>> USING btree
>> (code_id) where code_id not in ('P000','000') and code_id is not null;
>> ERROR: functions in index predicate must be marked IMMUTABLE

> BTW, this is on 8.2.9 Seems to work OK on 8.3.3.

I suppose code_id is varchar or some such?

Try "where code_id::text not in ...". There's an array type coercion
underlying the right-hand side of the NOT IN, and 8.2 had some problems
with correctly identifying the volatility of such coercions.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general