Insert an enum value into an unknown table with jOOQ

Comments on your existing attempts:

No typing at all

That doesn't work. jOOQ (or rather PostgreSQL) needs type information to bind an enum variable. This is a shame, of course, because the conversion from strings to enums could be seen as straight forward, so it could be done implicitly. But PostgreSQL currently doesn't work this way.

Column type as Enum.class + coercing or casting to Enum.class

This still doesn't work, because of the same reason. Now, jOOQ knows that we're dealing with an enum (it knew before, if the value was non-null), but we don't know the PostgreSQL enum type, which we need to cast the bind variable to.

Regarding "(Wut? I absolutely have set my dialect to SQLDialect.POSTGRES_9_5.)":

If you look at where the stack trace originates, it's when you pass Enum.class to DSL.field(). In that static method, there's no dialect in the context, so that's why this error message appears.

Solution

You were close:

Creating an ad-hoc enum in Java

When using EnumType in PostgreSQL, you need to also return the Schema reference. This is to distinguish between EnumType instances that have been generated with PostgreSQL or MariaDB/MySQL. This probably shouldn't be strictly necessary. Will check that through https://github.com/jOOQ/jOOQ/issues/7941

For now, try this:

private enum Direction implements EnumType {    NORTH, EAST, SOUTH, WEST;    @Override    public String getLiteral() {        return this.name();    }    @Override    public Schema getSchema() {        return MY_SCHEMA;    }    @Override    public String getName() {        return "direction";    }}