Subject: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 01:56:22 +0000

When I use java.sql.Timestamp value with PreparedStatement#setObject, PreparedStatement#executeUpdate throws an exception. It probably happens due to the conversion: driver converts the value to the string first, then claims ["Wow, man, you promised to supply a Timestamp but it's a text here! Fget off!"](https://github.com/pgjdbc/pgjdbc/blob/032d0e225a91e866d7dae680ebb784507392e803/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1497)

Error message:
``ERROR: column "modified_at" is of type timestamp with time zone but expression is of type text``

It would be great if you'd fix it. Thank you!

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

```

Code:

```
    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);
        }
    }

```
Result:
```
2025-01-17T17:58:29.682+02:00  INFO 4696 --- [           main] st.notexi.springtest.DbTest              : Starting DbTest using Java 17.0.8 with PID 4696 (started by viktor in /Users/viktor/Documents/work/java/SpringBootExample)
2025-01-17T17:58:29.683+02:00  INFO 4696 --- [           main] st.notexi.springtest.DbTest              : No active profile set, falling back to 1 default profile: "default"
2025-01-17T17:58:32.486+02:00  INFO 4696 --- [           main] st.notexi.springtest.DbTest              : Started DbTest in 2.981 seconds (process running for 3.606)
Java HotSpot(TM) 64-Bit Server VM warning: Sharing is only supported for boot loader classes because bootstrap classpath has been appended
2025-01-17T17:58:32.789+02:00  INFO 4696 --- [           main] st.notexi.springtest.DbTest              : Driver version 42.7.5
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

org.postgresql.util.PSQLException: 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
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.5.jar:42.7.5]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.5.jar:42.7.5]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.5.jar:42.7.5]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517) ~[postgresql-42.7.5.jar:42.7.5]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434) ~[postgresql-42.7.5.jar:42.7.5]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194) ~[postgresql-42.7.5.jar:42.7.5]
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:155) ~[postgresql-42.7.5.jar:42.7.5]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-5.1.0.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
	at st.notexi.springtest.DbTest.test(DbTest.java:81) ~[test-classes/:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:728) ~[junit-platform-commons-1.10.5.jar:1.10.5]
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:218) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:214) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:139) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69) ~[junit-jupiter-engine-5.10.5.jar:5.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:156) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:160) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:160) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54) ~[junit-platform-engine-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:198) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:169) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:93) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:58) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:141) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:57) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:103) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:85) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.DelegatingLauncher.execute(DelegatingLauncher.java:47) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:63) ~[junit-platform-launcher-1.10.5.jar:1.10.5]
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57) ~[junit5-rt.jar:na]
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38) ~[junit-rt.jar:na]
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11) ~[idea_rt.jar:na]
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35) ~[junit-rt.jar:na]
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232) ~[junit-rt.jar:na]
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55) ~[junit-rt.jar:na]

```

Update: it does not work with the same error neither with ``ps.setObject(2, ts, Types.TIMESTAMP)`` nor with ``ps.setTimestamp(2, ts)``

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 02:24:57 +0000

It would help if you provide sample code

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:29:09 +0000

Sure, I am aware of that, will do till Monday. Put it here as a kind of placeholder so far. Hope it's still clear enough even without it.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:41:15 +0000

Well, the code you referenced works fine, so I'd need a repro to see what's really going on.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:47:55 +0000

I suspect it might be due to the string format. The app I use retrieves data from another source and this timestamp contains some tz info and string format is like "2000-01-01 10:30:00+2" or something. Duno, will make mve this weekend because a lot of work now. Thank you!

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 12:10:23 +0000

So you are using setObject for all of those parameters?
Can I see the java code ?

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 12:18:40 +0000

Got an idea to replace UPDATE and INSERT values to the "?" and bind them as well and check would it help or not.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:20:50 +0000

Sorry for delay. Here's the first MVE for the original case:

```
create table test
(
    id     bigint primary key,

```
    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() throws SQLException
    {
        try (Connection conn = dataSource.getConnection())
        {
            PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);

            Integer id = 1;

            ps.setObject(1, id);
            ps.setObject(2, ts);
            ps.setObject(3, amount);
            ps.setObject(4, id);
            ps.executeUpdate();
        } catch (Exception e)
        {

```

```
2025-01-17T15:20:27.039+02:00 ERROR 48332 --- [           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: 177

  Position: 177
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.4.jar:42.7.4]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.4.jar:42.7.4]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.4.jar:42.7.4]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517) ~[postgresql-42.7.4.jar:42.7.4]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434) ~[postgresql-42.7.4.jar:42.7.4]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194) ~[postgresql-42.7.4.jar:42.7.4]
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:155) ~[postgresql-42.7.4.jar:42.7.4]
	at st.notexi.springtest.DbTest.test(DbTest.java:78) ~[test-classes/:na]

```

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:22:52 +0000

And for the second, almost the same:

```

    @Test

            Integer id = 1;

            ps.setObject(1, id);
            ps.setObject(2, null);
            ps.setObject(3, amount);
            ps.setObject(4, id);
            ps.executeUpdate();
        } catch (Exception e)
        {

```

```
2025-01-17T15:21:51.921+02:00 ERROR 49518 --- [           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: 177

	at st.notexi.springtest.DbTest.test(DbTest.java:79) ~[test-classes/:na]

```

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:24:18 +0000

I'll delete my previous code as it is proprietary if you don't mind. Hope these examples are enough. 
If you have any ideas about at least temporary workarounds, please let me know. Thank you!

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:25:36 +0000

And please tell me, do I need to open another bug report for a "null" case?

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:30:33 +0000

Sorry, forgot to include:

```
    @Test

            Integer id = 1;

            ps.setObject(1, id);
            ps.setObject(2, null);
            ps.setObject(3, amount);
            ps.setObject(4, id);
            ps.executeUpdate();
        } catch (Exception e)
        {

```

```
2025-01-17T15:29:44.473+02:00  INFO 52362 --- [           main] st.notexi.springtest.DbTest              : Driver version 42.7.5
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:32:10 +0000

So after looking at your code I understand the problem.

I'll try to spend some time on it

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:33:25 +0000

Well, workaround wit explicit bindings works fine in both cases:

```

                set "ts"=?,
                    amount=?
                values (?, ?, ?)
            """;

    @Test

            Integer id = 1;

            ps.setObject(1, id);
            ps.setObject(2, ts);
            ps.setObject(3, amount);
            ps.setObject(4, id);

            ps.setObject(5, ts);
            ps.setObject(6, amount);
            ps.setObject(7, id);
            ps.setObject(8, ts);
            ps.setObject(9, amount);

            ps.executeUpdate();
        } catch (Exception e)
        {

```

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:37:48 +0000

> I'll try to spend some time on it

Thank you! I owe you at least a mug of beer  )

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 13:48:35 +0000

Using psql this
```
merge into test as dst                                                                                                                                                                                                                                   using (select 1  as id,                                                                                                                                                                                                                                           '2025-01-17 08:39:48.951484-05' as ts,                                                                                                                                                                                                              5 as amount) src                                                                                                                                                                                                                      on dst.id=1                                                                                                                                                                                                                                         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);
```
Gives the same error. I don't think the driver has enough information to use anything but text in the first query.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 14:10:56 +0000

Well, it’s what i was afraid of. So, it is postgres error. Well, thank you for your help, will open it there

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 14:17:37 +0000

Well, but why converting it to the localdatetime works then? The driver passes different parameters probably? Why is it working with LocalDateTime?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 14:31:40 +0000

Let me put a small test case together to see if we can figure it out. We don't need as much code as you have
Just 5 or six lines of java code

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 14:51:55 +0000

Thank you. Sorry, this is the minimal example I could imagine.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 14:54:44 +0000

that works

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 14:57:34 +0000

Sorry, not sure I understand. Just a little remark, you're getting the error because you're really using text instead of the timestamp. In java, I use timestamp, not text. An example like this works fine:

```
merge into test as dst
using (select 1     as id,
              to_timestamp('2025-01-17 08:39:48', 'YYYY-MM-DD hh24:mi:ss')::timestamp with time zone at time zone
              'EET' as ts,
              5     as amount) src
on dst.id = 1
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)
```

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 15:11:40 +0000

Also, this works:

```
do
$$
    declare
        amt integer;
    begin
        amt := null;
        merge into test as dst
        using (select 1       as id,
                      to_timestamp('2025-01-18 08:39:48', 'YYYY-MM-DD hh24:mi:ss')::timestamp with time zone at time zone
                      'EET'   as ts,
                      amt as amount) src
        on dst.id = 1
        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);
    end;
$$;
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 15:12:43 +0000

>             log.error(e.getMessage(), e);

So this code works. What code doesn't?

Also not sure why you are double quoting columns ?

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 15:18:24 +0000

The original code I submitted [here](https://github.com/pgjdbc/pgjdbc/issues/3482#issuecomment-2598356622) does not work. The one with the "src" references in insert/update. Does not work both with Timestamp and null values.
Quoting is just a habit. Do not remember why, I just remember that I should do it for the last 30 years. Anyways, it does not work either with or without quotes.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 15:32:31 +0000

Ok, well null makes sense since there is absolutely no type information associate with null which is why there is https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html#setNull-int-int- and you have to specify the parameter.

when you say `Timestamp` what would this line be `Timestamp ts = Timestamp.valueOf(LocalDateTime.now());`

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 15:59:45 +0000

Ok, let's put null aside. Timestamp does not work either. Since my original question was about timestamp, I'll add the mve to the report then.
Done.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 16:17:24 +0000

OK, so by default parameters are sent as text.
Neither the driver nor the server know ahead of time that 

select ? as ts is supposed to be a timestamp. 

The error message is `Hint: You will need to rewrite or cast the expression.`
so ```
 merge into test as dst using (select $1 as id, $2::timestamp as ts, $3 as amount) src on dst.id=$4 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)
```
Will work

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:11:15 +0000

Yes, that's what I observe - driver recognizes object as instanceof timestamp, then converts it to text losing all its type information. But why? And why OffsetDateTime works then? Doesn't diver send it as text? And since it is the instance of the timestamp, why driver does not do all required casts? As I remember, according to the standard, setObject should work this way:

https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html#setObject-int-java.lang.Object-

> The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database.

I see no mentions of the "text" here. And text is definitely is not the corresponding SQL type for the java.sql.Timestamp. Obviously, java.sql.Timestamp is the SQL type by itself.

Sorry, I was working with oracle for many years and it's a kina new problem and approach for me. Just trying to understand.As for me, it's clearly a bug. But of course I might be wrong, that's why I'm asking about "official" interpretation of the standards I quoted above.

---

From: "Zegarek0 (@Zegarek0)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:11:53 +0000

@davecramer It's best to cast all three, not just the `ts`. [OP opened a thread on Stack Overflow](https://stackoverflow.com/a/79365499/5298879), where I added an explanation what's happening exactly, including the null case. If you also cast the `amount::int` it also solves the problem where Postgres complains you're giving it a `text` when you gave it a *`null`*.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:13:46 +0000

> [@davecramer](https://github.com/davecramer) It's best to cast all three, not just the `ts`. [OP opened a thread on Stack Overflow](https://stackoverflow.com/a/79365499/5298879), where I added an explanation what's happening exactly, including the null case.

@Zegarek0 

I agree that according to the specs it might be required to specify the type of the null value. It's ok. But my report was about java.sql.Timestamp, and as I see it now, it is definitely a bug.

---

From: "Zegarek0 (@Zegarek0)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:20:30 +0000

@BbIKTOP On SO, I'm answering "should it work the way I am trying to use it? I. e., can I use src.columnname reference in the INSERT and UPDATE?" - that's a yes, as long as you do the cast. Here I'm only addressing @davecramer's suggestion to cast the `ts::timestamptz`, and taking the opportunity to link the SO thread, since the SO thread is already linked to the discussion here. 

Don't take it as an attempt to shut it down or suggest my answer there is in any way meant to close the discussion here, or that it even provides the full picture - I only touched on the Postgres part.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:25:49 +0000

@Zegarek0 Yes, I read your answer but Dave gave me better - he gave me a clue to read how it **should** work according to the specs. And as I see, null might not work but Timestamp should work without any casts.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:32:54 +0000

@Zegarek0 also, it is really difficult to discuss on SO as comments are limited. My point is, that for the Timestamp's case, both driver and Postgres have all required information about types. Like, when I do the same using pgsql and Postgres also has the type info and everything is working. 
```
do
$$
    declare
        amt_val integer;
        ts_val  timestamp with time zone;
    begin
        amt_val := null;
        ts_val := to_timestamp('2025-01-18 08:39:48', 'YYYY-MM-DD hh24:mi:ss') at time zone 'EET';
        merge into test as dst
        using (select 1       as id,
                      ts_val  as ts,
                      amt_val as amount) src
        on dst.id = 1
        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);
    end;
$$;
```
I agree that in case of the null, either driver or Postgres should be a little bit intelligent and to do some work finding out what type is really required (as Oracle does). And since for null it is not required by the standard, it could be done the way it is done now. But for Timestamp it is really strange, why driver, having all the information regarding object's type, just discards it and sends the value as a text.
Anyways. I'd like to see the "official" opinion" - is it considered a bug?

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 17:46:52 +0000

Btw I remembered why I use quotes. In theory the name could be case sensitive.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 18:25:03 +0000

One of the problems is that postgres  supports 2 timestamp types; JAVA and JDBC only support one. Usually the backend can figure out what you want to do but it appears in a merge it has difficulties

For instance this:

```
try (Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/test", "test", "test")) {
            PreparedStatement ps = conn.prepareStatement("insert into test ( id, ts, amount ) values (?,?,?)");
            Integer id = 2;
            Timestamp ts = Timestamp.valueOf(LocalDateTime.now());
            Integer amount = 123;

            ps.setObject(1, id);
            ps.setTimestamp(2, ts);
            ps.setObject(3, amount);

            ps.executeUpdate();
            
        }
``` 
works fine but merge doesn't seem to. So might be worth posting this to the pgsql-hackers list.

Dave

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 18:31:34 +0000

Thank you Dave!

Yes, simple queries work, but [there's also a bug](https://github.com/pgjdbc/pgjdbc/issues/3464) I have filed a month ago with no responses.
So, do you think it is not a driver but Postgres problem?
But why pl block works fine then? Also, please tell me more about these 2 timestamp types. Are they with and without timezone or something else?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 19:03:07 +0000

Well your bug shows the problem. 
We do need to handle timestamps better, it's just not been addressed.
I do think it is a postgres problem, at least there's nothing we can do from a driver perspective.
The pl block probably binds it to a specific type. I would have to look at the code. PL/Pgsql runs in the server, not through the protocol so it has more information.

As for the two types, yes one with TZ and one without. There are oddities with TZ. The data is actually stored at UTC and converted to the client timezone.

Cheers,
Dave

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 20:55:57 +0000

PL runs on the server, so every query does. I surely understand the difference which isn't probably too big. At least in oracle, there are separate parsers and executors for PL and ordinary queries, I suppose PG does the same. The actual thing probably is that PL knows the type and maybe just sets it properly. And maybe it uses another protocol, which I doubt to be true, at least from the architectuarl perspective. I do not know details about protocol used for remote connections, but there should be a possibility to explicitly specify the type as well. Just guessing.
I am aware that PG stores timestams without TZ by converting them to the UTC. So, there's actually less work and just one timestamp' type. Like, 'if it is with TZ, convert to UPC', then 'store it without TZ as it is UTC'
Well, what would you advise? I am implementing a workaround with explicit bindings in my code, it's not so trivial because query is generated, but I see no other ways to make it work so far...

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 21:20:10 +0000

using (select ? as id, ? as ts, ? as amount) src
everything in the paren is a stand alone subquery and there is no context, so the 'YYYY-MM-DD HH:MM:SS.SSSSS' of the timestamp is assumed "unknown" and cast to "text"

> I am aware that PG stores timestams without TZ by converting them to the UTC. So, there's actually less work and just one timestamp' type. Like, 'if it is with TZ, convert to UPC', then 'store it w…

Actually PG stores timestamps *WITH* TZ by converting them to UTC. Timestamps *WITHOUT* TZ are just stored as presented

as for what you need to do, it would appear you need to explicitly cast.

Dave

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Fri, 17 Jan 2025 21:56:00 +0000

as bindings have their type, it could be propagated inside nested blocks, as it done for ordinary queries. So, the problem is this propagation.
I see explicit cast could be done the Postgres ':type' way, which is not portable, or by binding each parameter explicitly, to prevent the necessity of the type propagation. I am currently working on the second, which, as I stated, a bit complicated because query is generated and I am trying to imagine how to get the type info from the source. I probably need to store some 'metadata' or something. Maybe pl block could help but I'm worrying about performance - this app should process a lot of data as fast as possible.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 18 Jan 2025 12:07:15 +0000

try  ```
insert into test1 (id, ts,amount)  
 values ( ?,?,?  ) 
 on conflict (id)  
do update                                                                                                                                                                                                                             
 set ts = excluded.ts, amount=excluded.amount;

Otherwise this is a problem for the server. The client can't fix this
```

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Sat, 18 Jan 2025 12:51:26 +0000

Unfortunately. This "upsert" requires _one constraint_. In my case, generated queries could contain more than one and condition could be just condition, on the column without constraints. As it is generated, program actually knows little about merge conditions. So, will stay with direct bindings, looks like in this case type information isn't lost and query is working. Thank you! Probably the last question - where could I ask about fixing? I understand that there's almost no chances, but anyways. pgsql-hackers?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 18 Jan 2025 12:53:08 +0000

Yes, you would need to ask on pgsql-hackers.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Sun, 19 Jan 2025 22:17:48 +0000

> using (select ? as id, ? as ts, ? as amount) src everything in the paren is a stand alone subquery and there is no context, so the 'YYYY-MM-DD HH:MM:SS.SSSSS' of the timestamp is assumed "unknown" and cast to "text"
> 
Just to explain my point a bit further. Suppose, there's grammar that defaults undeclared variables to text. In this case, your example is correct, like
```
somevar=3.3;
```
might produce an error, although it is easy to set the type to the type used in the first assignment.
But in my case, after parsing, the type is actually known as variable is used in the assignment. So, variable type definitely should be equal to the column's type it is assigned to. The only potential error I see here is some kind of "type mismatch" if column types differ in inserts/updates. As parser parses whole statement before execution, it is definitely possible to check and assign correct type after parsing. Looks like it just does not do that.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 20 Jan 2025 00:00:05 +0000

Sorry, I think I was a bit obtuse. The driver is fine sending things in text. Normally the server can figure out the type and it casts it correctly.

The issue here is that the way the server works there is no context in the using clause and everything there is text. As I said you need to bug the server guys.

---

From: "BbIKTOP (@BbIKTOP)" <[email-masked]>
Date: Mon, 20 Jan 2025 08:55:51 +0000

Well, Dave, as a developer for about last 40 years, I totally understand your point ;) I mean "it's somewhere someone's else problem" I did that so many times as well )

Please understand mine. As soon as this:

```
root@d2c635331de7:/# psql localdev  dev
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

localdev=> merge into localdev.test as dst
using (select 3 as "id",
              to_timestamp('2025-01-18 08:39:48', 'YYYY-MM-DD hh24:mi:ss') at time zone 'EET' as "ts",
              44 as amount) src
on dst."id"=src.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")
;
MERGE 1
localdev=> 
```
works, I still insist it is a driver's problem. I could make a simple C program to check, but as for me it is quite obvious, isn't it?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 20 Jan 2025 11:37:47 +0000

So I was able to confirm your assertion that it could be fixed in the driver. That said you've run into another problem which I eluded to earlier. The reason we send it in text is because we don't know if you want Timestamp with or without TZ so we send it as text and let the server figure it out. In this case the server can't figure it out. 
I'm open to suggestions, however please look at the history of this discussion, there are quite a few issues and PR's referring to timestamp. If only the spec supported 2 timestamps