How to Fix onDuplicateKeyUpdate() Issue in PostgreSQL 14 and Restrict jOOQ to a Specific PostgreSQL Version

[k s]

I encountered an issue when using onDuplicateKeyUpdate() on a table that has both a primary key and a separate unique constraint. The following error occurred:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "merge" Position: 1 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)

After investigating, I found that the MERGE statement is supported starting from PostgreSQL 15. As a workaround, I believe using onConflict would resolve the issue for PostgreSQL 14.

However, to prevent similar syntax errors due to PostgreSQL version differences in the future, I would like to know if there is a way to explicitly configure jOOQ to target PostgreSQL 14.

Is there an option to specify the PostgreSQL version for jOOQ to ensure compatibility?

Thank you for your help!

Best regards,

[Lukas Eder]

Thanks for your message

Please always consider the support matrix to see what your jOOQ version / edition supports. The SQLDialect.POSTGRES dialect always corresponds to the latest supported version:

https://www.jooq.org/download/support-matrix

You can use SQLDialect.POSTGRES_14 to explicitly target PostgreSQL 14:

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/SQLDialect.html#POSTGRES_14

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/07718f26-6def-4cd9-a00b-b42a1798868fn%40googlegroups.com.

[k s]

Thank you.

In the OSS version, it seems that explicitly changing the target is not possible.
I will consider either downgrading jOOQ, upgrading the server, or handling it with onConflict.

2025年2月17日月曜日 18:27:27 UTC+9 lukas...@gmail.com:

[Lukas Eder]

On Thu, Feb 20, 2025 at 10:23 AM k s <system....@gmail.com> wrote:

Thank you.

In the OSS version, it seems that explicitly changing the target is not possible.

Indeed, the jOOQ Open Source Edition only supports one RDBMS version per jOOQ version. RDBMS version support is available starting with the jOOQ Express Edition and in higher tier price plans. 

Best Regards,

Lukas

[David Bullock]

The Express edition is really a very good deal!

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO74G%3DMdVPPgmT6aQtSPu9EL%2BXmUdaKo1%2Bvup_EqfO%2BYbQ%40mail.gmail.com.