Insert an enum value into an unknown table with jOOQ

Question

Given a table with an enum column, like this:

CREATE TYPE DIRECTION AS ENUM ('NORTH', 'EAST', 'SOUTH', 'WEST');

CREATE TABLE enum_table (
    direction DIRECTION NOT NULL
);

How do I insert into the said table using jOOQ without generating Java code for the whole table? For this particular instance, I cannot (yet) simply generate the code due to other technical restrictions. I could copy-paste a piece of generated code (a type definition e.g.) if that helps, but the whole table is too much.

What I tried:

• No typing at all:

  context.insertInto(table("enum_table"))
      .columns(field("direction"))
      .values("west")
      .execute();
  

  As expected, this throws on incompatible types:

  org.jooq.exception.DataAccessException: SQL [insert into enum_table (direction) values (?)]; ERROR: column "direction" is of type direction but expression is of type character varying.

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

  context.insertInto(table("enum_table"))
      .columns(field("direction", Enum.class))
      .values(DSL.coerce("west", Enum.class))  // or DSL.cast(), same result
      .execute();
  

  which throws this:

  org.jooq.exception.SQLDialectNotSupportedException: Type class java.lang.Enum is not supported in dialect DEFAULT.

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

• Creating an ad-hoc enum in Java:

  private enum Direction implements EnumType {
      NORTH, EAST, SOUTH, WEST;
  
      @Override
      public String getLiteral() {
          return this.name();
      }
  
      @Override
      public String getName() {
          return "direction";
      }
  }
  
  // and then
  context.insertInto(table("enum_table"))
      .columns(field("direction", Direction.class))
      .values(Direction.WEST)
      .execute();
  

  Also tried an alternative - same result:

  .columns(field("direction", SQLDataType.VARCHAR.nullable(false).asEnumDataType(Direction.class)))
  

  Throws the incompatible types exception again:

  org.jooq.exception.DataAccessException: SQL [insert into enum_table (direction) values (?)]; ERROR: column "direction" is of type direction but expression is of type character varying.

---

Is there any way to insert into an "unknown" (not generated) table with an enum column using jOOQ?

Answer 1

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";
    }
}