I am trying to upsert the rows which I retrieved from one table of a database to the identical table in another database. Both databases are postgresql.
I just want to retrieve whole columns of the rows of the table as records and simply upsert it to the table in another database.
I have written the following code :
try {
String sourceDbUrl = "jdbc:postgresql://localhost:5432/sourcedb";
String sourceDbUsername = "postgres";
String sourceDbPassword = "admin";
Connection sourceConnection = DriverManager.getConnection(sourceDbUrl, sourceDbUsername, sourceDbPassword);
Configuration sourceConfig = new DefaultConfiguration().set(sourceConnection).set(SQLDialect.POSTGRES);
String targetDbUrl = "jdbc:postgresql://localhost:5432/targetdb";
String targetDbUsername = "postgres";
String targetDbPassword = "admin";
Connection targetConnection = DriverManager.getConnection(targetDbUrl, targetDbUsername, targetDbPassword);
Configuration targetConfig = new DefaultConfiguration().set(targetConnection).set(SQLDialect.POSTGRES);
String sourceSchema = "public";
String targetSchema = "public";
Table<Record> sourceTable = DSL.table(DSL.name(sourceSchema, "mytable"));
Table<Record> targetTable = DSL.table(DSL.name(targetSchema, "mytable"));
DSLContext sourceDslContext = DSL.using(sourceConfig);
DSLContext targetDslContext = DSL.using(targetConfig);
String customSelectQuery = "SELECT * FROM " + sourceSchema + ".mytable";
Result<Record> records = sourceDslContext.fetch(customSelectQuery);
for (Record record : records)
targetDslContext
.insertInto(targetTable)
.set(record)
.onDuplicateKeyUpdate()
.set(record)
.execute();
} catch (Exception e) {
e.printStackTrace();
}
I can see that I retrieve the records successfully.
However, somehow the record values are sent empty inside the upsert query created by jooq inside for loop and I got the following exception :
org.jooq.exception.DataAccessException: SQL [insert into "public"."mytable" values () on conflict ([unknown primary key]) do update set [ no fields are updated ]]; ERROR: syntax error at or near ")" Position: 40 at org.jooq_3.16.1.POSTGRES.debug(Unknown Source) at org.jooq.impl.Tools.translate(Tools.java:3089) at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:670) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349) at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:115) at Main.main(Main.java:48) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 40 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153) at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:414) at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:961) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:335) ... 2 more
I appreciate for your comments.
Thanks,
This article on why you should use jOOQ with code generation explains why you ran into this problem. In short, without primary/unique key meta data attached to your tables, jOOQ cannot emulate the MySQL ON DUPLICATE KEY UPDATE clause on your PostgreSQL dialect.
But then again, why even use that? You're using PostgreSQL, and jOOQ has native support for PostgreSQL's ON CONFLICT clause (or also MERGE for more complex cases), so use the native syntax, instead.