Re: 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