Thread: IN clause on BYTEA column works against table directly but gives error against view

I've got a SELECT with a WHERE clause using IN against a BYTEA column
which works when I'm querying against the table directly. However when I
substitute an equivalent view in my query (a view made of several inner
joins which simply carry over that same BYTEA column), I get the error:

operator does not exist: "integer = bytea"

I've googled this error and read how certain syntax against BYTEA which
worked in older versions of PostgreSQL stopped working at some point
when some implicit conversion rules were tightened up. I'm not sure of
the relevance of this to my situation given the change in behavior
between table vs view.

My query is occurring via JDBC, using a prepared statement, where I'm
passing a byte array for each parameter in the IN clause, using the
setObject(int, Object) method.

I've tried this on PostgreSQL versions 8.3.1, and 8.4.5, and have tried
various versions of the JDBC drivers.

I'll try working around this by having my code create escaped octal
syntax instead of passing a byte array, or possibly by passing a SQL
type code. But I thought I should see if anyone had some idea why a
table vs a view should produce different behavior like this, and if
there were any other workarounds or caveats about what may be going on
that I should be aware of...

Regards,

Eric

Eric Schwarzenbach <subscriber@blackbrook.org> wrote:

> I've got a SELECT with a WHERE clause using IN against a BYTEA
> column which works when I'm querying against the table directly.
> However when I substitute an equivalent view in my query (a view
> made of several inner joins which simply carry over that same
> BYTEA column), I get the error:
>
> operator does not exist: "integer = bytea"

> My query is occurring via JDBC, using a prepared statement

Please submit a small, self-contained class which creates any needed
tables and views and then runs a query generates the error.  The
smaller and simpler, the better.

-Kevin