Re: MySQL search query is not executing in Postgres DB

On 08/29/2012 01:32 AM, Robert Haas wrote:
> On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Also, right at the moment it's not clear to me whether there are any
>> other cases besides integer literal vs smallint argument. I think
>> that's the only particularly surprising case within the numeric
>> hierarchy --- and for non-numeric types, the literal is generally going
>> to start out "unknown" so the whole problem doesn't arise. I feel
>> uncomfortable trying to invent general-purpose solutions to problems
>> we have only one instance of ...
>
> The other case that comes up regularly is someone trying to pass some
> kind of number to a function such as LPAD(). There is only one LPAD()
> so no ambiguity exists, but PostgreSQL doesn't even see that there's a
> candidate.

Allowing Pg to assign parameters or fields by using the
normally-only-explicit casts where no ambiguity exists would be *really*
helpful in other areas, too.

In particular, this applies with assignment of fields from `text' input,
too. PostgreSQL can be incredibly frustrating to work with from
Java/JDBC where everything goes through protocol-level parameterised
statements, because you can't use Java `String' types via
PreparedStatement.setString() to assign to, say, an `xml' or `json'
field, you have to use `setObject()'.

That's OK (ish) when working with PgJDBC directly, but it breaks code
that expects this to work like it does in other databases where
setString(...) can be used to assign to anything that's castable from
varchar.

Pg doesn't allow `unknown' to be passed as the type of a parameterised
statement, so the JDBC driver can't work around this by passing such
entries as fields of "unknown" type and letting the server work it out.
It'd instead have to ask the server "what are the permissible types for
the placeholder $1 in this query" ... which AFAIK isn't possible, and
would require extra round trips too.

I currently work around this by creating additional implicit casts where
I need them, eg text->xml, text->json. It'd be lovely not to have to do
that, though.

--
Craig Ringer