pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
48+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 01:56 "BbIKTOP (@BbIKTOP)" <[email protected]>
0 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 01:56 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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/...)
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)``
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 02:24 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 02:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
It would help if you provide sample code
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 09:29 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 09:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 09:41 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 09:41 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well, the code you referenced works fine, so I'd need a repro to see what's really going on.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 09:47 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 09:47 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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!
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 12:10 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 12:10 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
So you are using setObject for all of those parameters?
Can I see the java code ?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 12:18 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 12:18 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Got an idea to replace UPDATE and INSERT values to the "?" and bind them as well and check would it help or not.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:20 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Sorry for delay. Here's the first MVE for the original case:
```
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() throws SQLException
{
try (Connection conn = dataSource.getConnection())
{
PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);
Integer id = 1;
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-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
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: 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 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:78) ~[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]
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:22 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:22 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
And for the second, almost the same:
```
@Test
public void test() throws SQLException
{
try (Connection conn = dataSource.getConnection())
{
PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);
Integer id = 1;
Timestamp ts = Timestamp.valueOf(LocalDateTime.now());
Integer amount = 123;
ps.setObject(1, id);
ps.setObject(2, null);
ps.setObject(3, amount);
ps.setObject(4, id);
ps.executeUpdate();
} catch (Exception e)
{
log.error(e.getMessage(), 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
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: 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 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:79) ~[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]
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:24 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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!
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:25 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
And please tell me, do I need to open another bug report for a "null" case?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:30 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:30 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Sorry, forgot to include:
```
@Test
public void test() throws SQLException
{
try (Connection conn = dataSource.getConnection())
{
log.info("Driver version {}", conn.getMetaData().getDriverVersion());
PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);
Integer id = 1;
Timestamp ts = Timestamp.valueOf(LocalDateTime.now());
Integer amount = 123;
ps.setObject(1, id);
ps.setObject(2, null);
ps.setObject(3, amount);
ps.setObject(4, id);
ps.executeUpdate();
} catch (Exception e)
{
log.error(e.getMessage(), e);
}
}
```
```
2025-01-17T15:29:44.473+02:00 INFO 52362 --- [ main] st.notexi.springtest.DbTest : Driver version 42.7.5
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:32 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 13:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
So after looking at your code I understand the problem.
I'll try to spend some time on it
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:33 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well, workaround wit explicit bindings works fine in both cases:
```
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"=?,
amount=?
when not matched then
insert ("id", "ts", "amount")
values (?, ?, ?)
""";
@Test
public void test() throws SQLException
{
try (Connection conn = dataSource.getConnection())
{
log.info("Driver version {}", conn.getMetaData().getDriverVersion());
PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);
Integer id = 1;
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.setObject(5, ts);
ps.setObject(6, amount);
ps.setObject(7, id);
ps.setObject(8, ts);
ps.setObject(9, amount);
ps.executeUpdate();
} catch (Exception e)
{
log.error(e.getMessage(), e);
}
}
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:37 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 13:37 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> So after looking at your code I understand the problem.
>
> I'll try to spend some time on it
Thank you! I owe you at least a mug of beer )
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 13:48 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 13:48 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 14:10 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 14:10 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well, it’s what i was afraid of. So, it is postgres error. Well, thank you for your help, will open it there
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 14:17 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 14:17 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well, but why converting it to the localdatetime works then? The driver passes different parameters probably? Why is it working with LocalDateTime?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 14:31 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 14:31 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 14:51 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 14:51 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Thank you. Sorry, this is the minimal example I could imagine.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 14:54 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 14:54 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
that works
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 14:57 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 14:57 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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)
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 15:11 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 15:11 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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;
$$;
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 15:12 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 15:12 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> Well, workaround wit explicit bindings works fine in both cases:
>
> ```
>
> 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"=?,
> amount=?
> when not matched then
> insert ("id", "ts", "amount")
> values (?, ?, ?)
> """;
>
> @Test
> public void test() throws SQLException
> {
> try (Connection conn = dataSource.getConnection())
> {
> log.info("Driver version {}", conn.getMetaData().getDriverVersion());
> PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);
>
> Integer id = 1;
> 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.setObject(5, ts);
> ps.setObject(6, amount);
> ps.setObject(7, id);
> ps.setObject(8, ts);
> ps.setObject(9, amount);
>
> ps.executeUpdate();
> } catch (Exception e)
> {
> log.error(e.getMessage(), e);
> }
> }
> ```
So this code works. What code doesn't?
Also not sure why you are double quoting columns ?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 15:18 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 15:18 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 15:32 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 15:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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());`
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 15:59 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 15:59 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 16:17 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 16:17 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:11 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 17:11 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.Ob...-
> 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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:11 "Zegarek0 (@Zegarek0)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: Zegarek0 (@Zegarek0) @ 2025-01-17 17:11 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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`*.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:13 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 17:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> [@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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:20 "Zegarek0 (@Zegarek0)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: Zegarek0 (@Zegarek0) @ 2025-01-17 17:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:25 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 17:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:32 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 17:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 17:46 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 17:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Btw I remembered why I use quotes. In theory the name could be case sensitive.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 18:25 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 18:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 18:31 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 18:31 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 19:03 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 19:03 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 20:55 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 20:55 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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...
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 21:20 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-17 21:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 without TZ as it is UTC'
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
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-17 21:56 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-17 21:56 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-18 12:07 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-18 12:07 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
```
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-18 12:51 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-18 12:51 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-18 12:53 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-18 12:53 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Yes, you would need to ask on pgsql-hackers.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-19 22:17 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-19 22:17 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-20 00:00 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-20 00:00 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-20 08:55 "BbIKTOP (@BbIKTOP)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: BbIKTOP (@BbIKTOP) @ 2025-01-20 08:55 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown
@ 2025-01-20 11:37 "davecramer (@davecramer)" <[email protected]>
46 siblings, 0 replies; 48+ messages in thread
From: davecramer (@davecramer) @ 2025-01-20 11:37 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 48+ messages in thread
end of thread, other threads:[~2025-01-20 11:37 UTC | newest]
Thread overview: 48+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-17 01:56 [pgjdbc/pgjdbc] issue #3482: When using java.sql.Timestamp for update, an exception thrown "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 02:24 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 09:29 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 09:41 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 09:47 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 12:10 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 12:18 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:20 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:22 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:24 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:25 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:30 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:32 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 13:33 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:37 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 13:48 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 14:10 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 14:17 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 14:31 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 14:51 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 14:54 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 14:57 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 15:11 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 15:12 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 15:18 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 15:32 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 15:59 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 16:17 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 17:11 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 17:11 ` "Zegarek0 (@Zegarek0)" <[email protected]>
2025-01-17 17:13 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 17:20 ` "Zegarek0 (@Zegarek0)" <[email protected]>
2025-01-17 17:25 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 17:32 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 17:46 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 18:25 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 18:31 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 19:03 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 20:55 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-17 21:20 ` "davecramer (@davecramer)" <[email protected]>
2025-01-17 21:56 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-18 12:07 ` "davecramer (@davecramer)" <[email protected]>
2025-01-18 12:51 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-18 12:53 ` "davecramer (@davecramer)" <[email protected]>
2025-01-19 22:17 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-20 00:00 ` "davecramer (@davecramer)" <[email protected]>
2025-01-20 08:55 ` "BbIKTOP (@BbIKTOP)" <[email protected]>
2025-01-20 11:37 ` "davecramer (@davecramer)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox