select into insert from values() with correct type casts using jOOQ

So values() in jOOQ gives you a table constructor. By default select() gives you all columns as they are, which means jOOQ will define as Field<object>

I think the way to think about this is that jOOQ at this point is to recognise two things. You can try to define fields in the output or you can accept objects and try to handle type cases on the way back. My preference would be for fields in the output.

One important aspect to consider here is that rows have to have the same types for a given column but there are cases where implicit casts in SQL may mess with that in jOOQ. For example, if you insert a numeric value in the first row and an integer in the second, the integer will be cast to numeric implicitly. Defining fields for the select avoids this problem. So in terms of the question I would define the fields separately and specifically them in the select from values section. That should solve that problem. With VALUES(), you are already on your own and jOOQ cannot infer types safely.

So with this, your code should become:

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(    row(        cast(null, COMPANY.COMPANY_NAME),        cast(null, PERSON.PERSON_NAME),        cast(null, EMPLOYMENT_CONTRACT.SALARY),        cast(null, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)    )).as("insert_values", "company_name", "person_name", "salary", "creation_time");Field<String> ivCompanyNmae = field("insert_values.company_name". Stirng.class);Field<Integer> ivSalary = field("insert_values.salary", Integer.class);...Insert<AffectedSubscriberRecord> insert = insertInto(EMPLOYMENT_CONTRACT)    .columns(EMPLOYMENT_CONTRACT.COMPANY_ID,        EMPLOYMENT_CONTRACT.PERSON_ID,        EMPLOYMENT_CONTRACT.SALARY,        EMPLOYMENT_CONTRACT.CREATION_DATE_TIME    )    .select(        select(            COMPANY.COMPANY_ID,            PERSON.PERSON_ID,            ivSalary,            ivCreatedTime        )        .from(insertValues)            .join(COMPANY).using(COMPANY.COMPANY_NAME)            .join(PERSON).using(PERSON.PERSON_NAME)     );

It's this point where jOOQ generates the casts. Unknowns can become archers but will then be explicitly cast correctly.