Re: PostgreSQL 8.2 Bug in casting varchar to int in SELECT ... WHERE IN ( ... )

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Note that neither SELECT will work on 8.3, because we no longer have an
> implicit cast from integer to text. I suppose the above is an oversight
> in how we handle IN-clauses (starting with 8.2 an IN-clause is
> transformed into an "x = ANY (...)" expression), but I don't think it's
> worth changing.

Yeah, the reason for the difference in behavior is that when there's
just one IN-list item, the parser reduces the thing to a plain "x = y"
expression, which succeeds in the same cases where writing it out that
way would work.  If there's more than one item then it wants to find a
common data type for all the expressions involved.  The implementation
details have changed (repeatedly) over time, but given that we're moving
to stricter behavior for implicit casting, I don't think there's really
anything to fix here.

You need to either cast the varchar to int, or quote the list items to
make them look like varchars, depending on which comparison semantics
you're really after.

            regards, tom lane