I'm using Jooq, with Postgres (i.e. Postgres dialect) The project is using Java 11, so, using Jooq Open Source, I'm limited to Jooq 3.16, am using 3.16.23.

I'm trying to do a batch upsert to a Postgres table, which has

1. pk fields
2. fields to be set when updating and inserting
3. fields to be set only when inserting

Here's some genericized code, adapted from what I was running:

    public static BatchBindStep startBatch(DSLContext dsl) {
    return dsl.batch(
            dsl.insertInto(MY_TABLE,
                    MY_TABLE.PK1,
                    MY_TABLE.PK2,
                    MY_TABLE.WRITEONLYWHENINSERTING,
                    MY_TABLE.WRITEALWAYS1,
                    MY_TABLE.WRITEALWAYS2,
            ).values(
                    null,
                    null,
                    null,
                    null,
                    (LocalDateTime) null
            ).onConflict(
                    MY_TABLE.PK1,
                    MY_TABLE.PK2,
            ).where(
                    MY_TABLE.WRITEALWAYS2.isNull()
                            .or(MY_TABLE.WRITEALWAYS2.lessOrEqual((LocalDateTime) null))
            ).doUpdate()
                    // TODO: DSL.excluded() is not available in our Jooq version
                    // TODO: without having a set() line for WRITEONLYWHENINSERTING, we get an Exception "No value specified for parameter xx."
                    //.set(MY_TABLE.WRITEONLYWHENINSERTING, DSL.excluded(MY_TABLE.WRITEONLYWHENINSERTING))
                    .set(MY_TABLE.WRITEALWAYS1, (Integer) null)
                    .set(MY_TABLE.WRITEALWAYS2, (LocalDateTime) null)
    );
}

    public static void bindOne(BatchBindStep batchBindStep, MyTableEncoder myTableEncoder) {
    batchBindStep.bind(
            myTableEncoder.getPk1(),
            myTableEncoder.getPk2(),
            myTableEncoder.getWriteOnlyWhenInserting(),
            myTableEncoder.getWriteAlways1(),
            myTableEncoder.getWriteAlways2()
    );
}

... and then later, execute() is called on the Batch.

In startBatch(), I initially called doUpdate() without calling DSL.excluded(). This led to a runtime exception, "No value specified for parameter 22." (in my real code). For the sample code above, it would be maybe "parameter 3", not sure.

So the column count (minus the PK columns) has to match, and didn't, due to the absence of the WRITEONLYWHENINSERTING field in the doUpdate() part.

At this point I should have used DSL.excluded() , which is not available in my Jooq version.

Then I looked at the syntax for MERGE, which in the docs is mentioned as being the generic syntax for doing an upsert. (I'm not very familiar with SQL MERGE, since I only came to know upsert in the context of Postgres, so I've only used ON CONFLICT)

The examples in the Jooq documentation all mention 2 tables, a SOURCE and a TARGET table, which seems to point to a different usage.

In my case of upsert, I merely have 1 table into which I want to insert/ or update... And then, when I attempt to write this, using dsl.mergeInto() , it is immediately marked in IntelliJ as "is deprecated since version 3.14 and marked for removal"

... which makes me wonder, is there any syntax for upsert in Postgres that will work, using this Jooq version, or should I wait until we have lifted our project to a highter Java version?

Update: I switched to the mergeInto() syntax. The apparent problem of the 2 tables in MERGE was solved by .usingDual() . The apparent problem of deprecated mergeInto() methods: it turned out that the methods with params TABLE, FIELD1, FIELD2 etc. are deprecated, but mergeInto(TABLE) is not. I got this to run, but at the time of batch execution, a PSQLException occurs: "ERROR: subquery in FROM must have an alias" . The best candidate for causing that was .usingDual() , which translates to a SELECT, but upon inspection, it has an alias. Now I'm stumped because none of remaining generated SQL contains FROM, or a SELECT ...

Update: The "subquery in FROM must have an alias" error seems spurious to me, and I suspect it may have to do with the highest postgres dialect version in this Jooq version being 14, whereas the actual postgres I'm using is 15.7 .

Update: Running the generated SQL by hand, the method .usingDual() , which in SQL becomes "using (select 1 as "one")", it becomes clear that the (select 1 as "one") is indeed the offending code which causes: [42601] ERROR: subquery in FROM must have an alias. I'm stumped, since it's the alias is clearly there ...

Update: So it seems my issue is identical with where the outcome was, IIUC, that a Jooq version which only supports Postgres dialects up to Postgres 14, should not be used with Postgres 15. Can anyone confirm that? I'm a little surprised by that because I thought that SQL generated which executes fine on Postgres 14 should still execute on Postgres 15.

I'm using Jooq, with Postgres (i.e. Postgres dialect) The project is using Java 11, so, using Jooq Open Source, I'm limited to Jooq 3.16, am using 3.16.23.

I'm trying to do a batch upsert to a Postgres table, which has

1. pk fields
2. fields to be set when updating and inserting
3. fields to be set only when inserting

Here's some genericized code, adapted from what I was running:

    public static BatchBindStep startBatch(DSLContext dsl) {
    return dsl.batch(
            dsl.insertInto(MY_TABLE,
                    MY_TABLE.PK1,
                    MY_TABLE.PK2,
                    MY_TABLE.WRITEONLYWHENINSERTING,
                    MY_TABLE.WRITEALWAYS1,
                    MY_TABLE.WRITEALWAYS2,
            ).values(
                    null,
                    null,
                    null,
                    null,
                    (LocalDateTime) null
            ).onConflict(
                    MY_TABLE.PK1,
                    MY_TABLE.PK2,
            ).where(
                    MY_TABLE.WRITEALWAYS2.isNull()
                            .or(MY_TABLE.WRITEALWAYS2.lessOrEqual((LocalDateTime) null))
            ).doUpdate()
                    // TODO: DSL.excluded() is not available in our Jooq version
                    // TODO: without having a set() line for WRITEONLYWHENINSERTING, we get an Exception "No value specified for parameter xx."
                    //.set(MY_TABLE.WRITEONLYWHENINSERTING, DSL.excluded(MY_TABLE.WRITEONLYWHENINSERTING))
                    .set(MY_TABLE.WRITEALWAYS1, (Integer) null)
                    .set(MY_TABLE.WRITEALWAYS2, (LocalDateTime) null)
    );
}

    public static void bindOne(BatchBindStep batchBindStep, MyTableEncoder myTableEncoder) {
    batchBindStep.bind(
            myTableEncoder.getPk1(),
            myTableEncoder.getPk2(),
            myTableEncoder.getWriteOnlyWhenInserting(),
            myTableEncoder.getWriteAlways1(),
            myTableEncoder.getWriteAlways2()
    );
}

... and then later, execute() is called on the Batch.

In startBatch(), I initially called doUpdate() without calling DSL.excluded(). This led to a runtime exception, "No value specified for parameter 22." (in my real code). For the sample code above, it would be maybe "parameter 3", not sure.

So the column count (minus the PK columns) has to match, and didn't, due to the absence of the WRITEONLYWHENINSERTING field in the doUpdate() part.

At this point I should have used DSL.excluded() , which is not available in my Jooq version.

Then I looked at the syntax for MERGE, which in the docs is mentioned as being the generic syntax for doing an upsert. (I'm not very familiar with SQL MERGE, since I only came to know upsert in the context of Postgres, so I've only used ON CONFLICT)

The examples in the Jooq documentation all mention 2 tables, a SOURCE and a TARGET table, which seems to point to a different usage.

In my case of upsert, I merely have 1 table into which I want to insert/ or update... And then, when I attempt to write this, using dsl.mergeInto() , it is immediately marked in IntelliJ as "is deprecated since version 3.14 and marked for removal"

... which makes me wonder, is there any syntax for upsert in Postgres that will work, using this Jooq version, or should I wait until we have lifted our project to a highter Java version?

Update: I switched to the mergeInto() syntax. The apparent problem of the 2 tables in MERGE was solved by .usingDual() . The apparent problem of deprecated mergeInto() methods: it turned out that the methods with params TABLE, FIELD1, FIELD2 etc. are deprecated, but mergeInto(TABLE) is not. I got this to run, but at the time of batch execution, a PSQLException occurs: "ERROR: subquery in FROM must have an alias" . The best candidate for causing that was .usingDual() , which translates to a SELECT, but upon inspection, it has an alias. Now I'm stumped because none of remaining generated SQL contains FROM, or a SELECT ...

Update: The "subquery in FROM must have an alias" error seems spurious to me, and I suspect it may have to do with the highest postgres dialect version in this Jooq version being 14, whereas the actual postgres I'm using is 15.7 .

Update: Running the generated SQL by hand, the method .usingDual() , which in SQL becomes "using (select 1 as "one")", it becomes clear that the (select 1 as "one") is indeed the offending code which causes: [42601] ERROR: subquery in FROM must have an alias. I'm stumped, since it's the alias is clearly there ...

Update: So it seems my issue is identical with https://github/jOOQ/jOOQ/issues/14582 where the outcome was, IIUC, that a Jooq version which only supports Postgres dialects up to Postgres 14, should not be used with Postgres 15. Can anyone confirm that? I'm a little surprised by that because I thought that SQL generated which executes fine on Postgres 14 should still execute on Postgres 15.

• postgresql
• jooq

1 Answer 1

Considering my issue is the same as
https://github/jOOQ/jOOQ/issues/14582
(and that issue was closed) , it boils down to a version problem. I'm using Postgres 15 and should not be using a Jooq version which only supports up to Postgres 14.