Re: BUG #18780: Bindings types are lost for complex queries - Mailing list pgsql-bugs

create table test
(    id     bigint primary key,    ts     timestamp with time zone,    amount integer
);

    private static final String MERGE_QUERY = """                        merge into test as dst            using (select ? as id,                          ? as ts,                          ? as amount) src            on dst.id=?            when matched then                update                set ts=src.ts,                    amount=src.amount            when not matched then                insert ("id", "ts", "amount")                values (src.id, src.ts, src.amount)            """;
    @Test    public void test()    {        try (Connection conn = dataSource.getConnection())        {            log.info("Driver version {}", conn.getMetaData().getDriverVersion());            PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);
            Integer id = 2;            Timestamp ts = Timestamp.valueOf(LocalDateTime.now());            Integer amount = 123;
            ps.setObject(1, id);            ps.setObject(2, ts);            ps.setObject(3, amount);            ps.setObject(4, id);
            ps.executeUpdate();        } catch (Exception e)        {            log.error(e.getMessage(), e);        }    }

2025-01-17T17:58:32.798+02:00 ERROR 4696 --- [           main] st.notexi.springtest.DbTest              : ERROR: column "ts" is of type timestamp with time zone but expression is of type text  Hint: You will need to rewrite or cast the expression.  Position: 169

On 20 Jan 2025, at 17:18, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Sunday, January 19, 2025, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      18780
Logged by:          Viktr
Email address:      vik@notexi.st
PostgreSQL version: 17.2
Operating system:   Linux d2c635331de7 6.10.14-linuxkit #1 SMP PREEMPT
Description:       

Hi there,

Actual discussion is here: https://github.com/pgjdbc/pgjdbc/issues/3482
In brief: I do understand that "select 1 as one, 2 as two, 3 as three" might
have lack of type info. But I suppose, in case these values are used in the
assignment, the type could be taken from the corresponding column type. Like
for "insert into sometable(one, two, three) select 1 as one, 2 as two, 3 as
three" it is obvious that types should match.

A bug report should be self-contained.  Only pointing to a 40 message long thread isn’t helping get the bug (well, feature) fixed.

The crux of that thread is your driver is sending along a text data typed value because it cannot decide whether timestamp or timestamptz is needed.  Implicitly casting text to something else isn’t going to happen.

This falls into a feature request, one that comes up from time-to-time, and doesn’t ever seem to meet anyone’s benefit/cost threshold for working on; or at least get pushed over the edge.

The underlying feature, I think, is you want the parse to be able to say “let the server decide the type” and the server reply with type info for unspecified parameters.  Or maybe accept the pseudo-type “unknown” at the API level.  In any case a thread to discuss a patch for such a change and how JDBC would leverage it to solve this problem would be the next step.  Searching the mailing lists for existing discussions may yield fruit too though I don;t know for certain.

David J.