Jooq Problem with Enums and different databases

Question

i am using jooq and i am trying to use a punishment enum, but when i try to do an insert statement, i get the following:

org.jooq.exception.DataAccessException: SQL [insert into "punishments" ("userId", "guildId", "punishment", "startData", "expired", "reason", "operator", "id", "endData") values (?, ?, ?, ?, ?, ?, ?, ?, ?)]; ERROR: column "punishment" is of type punishment but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 142

How i generate code

Using Gradle, flyway and jooq plugin i am able to migrate h2 database and then use jooq on it to generate code.

The following is my jooq configuration (it's inside the build.gradle)

jooq {
    version = '3.16.5'
    edition = nu.studer.gradle.jooq.JooqEdition.OSS

    configurations {
        main {
            generateSchemaSourceOnCompilation = true
            generationTool {
                jdbc {
                    driver = 'org.h2.Driver'
                    url = "jdbc:h2:file:${project.buildDir}/migration/h2;MODE=PostgreSQL"
                    user = 'SA'
                    password = ''
                    properties {
                        property {
                            key = 'ssl'
                            value = 'false'
                        }
                    }
                }
                generator {
                    name = 'org.jooq.codegen.JavaGenerator'
                    database {
                        name = 'org.jooq.meta.h2.H2Database'
                        inputSchema = 'PUBLIC'
                        outputSchema = ''
                    }
                    generate {
                        deprecated = false
                        records = true
                        immutablePojos = true
                        fluentSetters = true
                    }
                    target {
                        packageName = 'me.bluetree242.bbot.jooq'
                    }
                    strategy.name = 'org.jooq.codegen.DefaultGeneratorStrategy'
                    strategy {
                        matchers {
                            tables {
                                table {
                                    tableClass {
                                        transform = "PASCAL"
                                        expression = "\$0_TABLE"
                                    }
                                }
                            }
                            enums {
                                "enum" {
                                    enumClass {
                                        transform = "PASCAL"
                                        expression = "\$0_ENUM"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

The following is the sql used to create the table and enum

CREATE TYPE punishment AS ENUM ('MUTE', 'BAN', 'QUARANTINE', 'TIMEOUT', 'KICK');
CREATE TABLE punishments(
    "userId" BIGINT NOT NULL,
    "guildId" BIGINT NOT NULL,
    "punishment" punishment NOT NULL ,
    "expired" boolean NOT NULL,
    "operator" BIGINT,
    "reason" text,
    "endDate" BIGINT,
    "id" integer,
    "startDate" BIGINT NOT NULL
)

And this is my code to insert

            jooq.insertInto(PunishmentsTable.PUNISHMENTS)
                    .set(PunishmentsTable.PUNISHMENTS.USERID, userId)
                    .set(PunishmentsTable.PUNISHMENTS.GUILDID, guild.getId())
                    .set(PunishmentsTable.PUNISHMENTS.PUNISHMENT, type.asJooq())
                    .set(PunishmentsTable.PUNISHMENTS.STARTDATA, System.currentTimeMillis())
                    .set(PunishmentsTable.PUNISHMENTS.EXPIRED, !type.isSupportUndo())
                    .set(PunishmentsTable.PUNISHMENTS.REASON, operator.getReasonText(this))
                    .set(PunishmentsTable.PUNISHMENTS.OPERATOR, operator.getAsId())
                    .set(PunishmentsTable.PUNISHMENTS.ID, id)
                    .set(PunishmentsTable.PUNISHMENTS.ENDDATA, duration.toMillis() == 0 ? 0 : System.currentTimeMillis() + duration.toMillis())
                    .execute();

Database Used to run the program

generated with h2, uses postgreSQL when running the program

When Using postgres to generate the code

If i use postgres to generate the code, everything goes fine, but this isn't an option for me building must not require a database, local must be used (like h2)

Answer 1

To fix this issue, i had to stop using enums, and use something simmilar. I used a check instead of an enum, and varchar, and finally forced type to use java's enums.

Here is my new query to create the table, of course after purging the old one.

CREATE TABLE punishments(
    "userId" BIGINT NOT NULL,
    "guildId" BIGINT NOT NULL,
    "punishmentType" varchar NOT NULL check ("punishmentType" in ('MUTE', 'BAN', 'QUARANTINE', 'TIMEOUT', 'KICK')),
    "expired" boolean NOT NULL,
    "operator" BIGINT,
    "reason" text,
    "endDate" BIGINT,
    "id" integer,
    "startDate" BIGINT NOT NULL
)