Exclude supplied values from select using jooq

Question

I am trying to find a list of values present in a given list that are not present in a database table using jOOQ.

For example, I have values 1, 2, 3,4 present in my list.

The database already has entries for 1 and 2.

I am looking for a jOOQ query that will return me the results 3,4.

I understand this can be accomplished with the VALUES and EXCEPT terms in sql, and I know that these are present in jOOQ, and have been trying to replicate these, however the simple case explained here for using values uses a static list (whereas mine will be provided programatically), and I'm not sure if that is the cause, or if it is the more general syntax. I haven't found a good example of except in jOOQ online.

What I've tried:

List<String> valuesList

create.select()
    .from(values(getRowsFromList(valuesList)))
    .except(select(TABLE.VALUE).from(TABLE))
    .fetch(TABLE.VALUE)

values expects a vararg RowN object, so I'm converting the values as such

private RowN[] getRowsFromList(List<String> rows)
{
    return rows.stream().map(DSL::row).toArray(RowN[]::new);
}

Error:

java.lang.IllegalArgumentException: Field ("public"."table"."value") is not contained in Row ("v"."c1")

Using java 11, postgres 11 backend, jooq 3.13.12

Answer 1

Your query doesn't have a table.value column. It doesn't even have a value column. When you use set operations, the projected column names are those of the set operation's first subquery, e.g.:

SELECT a
FROM (VALUES (1), (2), (3), (4)) AS t (a)
EXCEPT
SELECT value
FROM table

This produces a table with a column a. But you didn't name your column of the first EXCEPT subquery, so the name is undefined (jOOQ and/or PostgreSQL may produce one, but I wouldn't rely on it).

The solution is to name your columns using a derived column list (the AS t (a) syntax I used above). This should fix your query:

create.select()
    .from(values(getRowsFromList(valuesList)).as("t", TABLE.VALUE.getName())
    .except(select(TABLE.VALUE).from(TABLE))
    .fetch(TABLE.VALUE)