Re: can't figure string compare result (using also custom C function)

On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
> SELECT idSpecie,nome FROM specienomi
> WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
> idspecie | nome
> ----------+-----------------------------------------
> 37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
>
> The same query but without one condition: no results.
>
> SELECT idSpecie,nome FROM specienomi
> WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
> idspecie | nome
> ----------+------
> (0 rows)
>
> I can't figure why, can someone tell me how investigate?

I've just looked back in the archives and noticed that you were asking
about functional indexes; you do know that if you change the definition
of a function that PG doesn't know to rebuild the index don't you?

That would exhibit the symptoms you're seeing; i.e. the first case is
using an index on "idspecie" and the second is using the (out-of-date)
functional index.

--
Sam http://samason.me.uk/