Ow Mun Heng <Ow.Mun.Heng@xxxxxxx> 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
