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

From: Eric Schwarzenbach <subscriber(at)blackbrook(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: IN clause on BYTEA column works against table directly but gives error against view
Date: 2010-12-27 19:39:57
Message-ID: 4D18EB8D.8040407@blackbrook.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2010-12-27 19:54:53 Re: IN clause on BYTEA column works against table directly but gives error against view
Previous Message Maxim Boguk 2010-12-26 05:29:06 Re: BUG #5797: Strange bug with hstore