Hi Ben, H2 and MySQL implement per-column enum types, unlike PostgreSQL, which supports true, reusable user-defined types. The two ways of declaring enums haven't been unified, for no real reason than "not top prio". I thought there was an issue to do this but haven't found it. Here is a newly created one: https://github.com/jOOQ/jOOQ/issues/7640
Regarding your approach, would you mind explaining your setup a little bit. You're using PostgreSQL DDL, so what does this have to do with H2? Regarding your second question, H2 doesn't support schema level enum types, so there is no way to be able to properly cast an enum bind variable on PostgreSQL, if the enum has not been generated using PostgreSQL, but you're doing that, so why would you get enum-per-tables? Ultimately, what matters is that org.jooq.EnumType is properly implemented including all the methods in there, for the enum to work on PostgreSQL. Lukas Am Di., 3. Juli 2018 um 01:58 Uhr schrieb <[email protected]>: > Hi Lukas, > > I am trying to migrate to use the newly released support for H2's enum. > The code is generated during the build and run against Postgres, so ideally > this keeps the differences between migration scripts minimal. Unfortunately > there are a few surprises. > > 1) Instead of a single enum for the given type, an enum-per-table is > generated. I skimmed the issue tracker and I see hints as to why this might > have been done, but not enough detail to explain why. > > Take an H2 migrations like, > > CREATE TYPE identity_type AS ENUM ('email', 'phone_number'); > > CREATE TABLE user_identity ( > id serial primary key, > user_id uuid NOT NULL, > identity varchar(255) NOT NULL, > identity_type identity_type NOT NULL, > FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE > ); > > CREATE TABLE access_code ( > id serial primary key, > identity varchar(255) NOT NULL, > identity_type identity_type NOT NULL, > code varchar(255) NOT NULL, > type varchar(255) NOT NULL, > identifiable boolean NOT NULL, > CHECK type in ('confirm', 'forgot_password', 'invite', 'register') > ); > > My naive expectation is that a single IdentityType enum is generated. I > would then be able to use this enum in queries to either table. Instead > there are two enums generated: AccessCodeIdentityType > and UserIdentityIdentityType. These types are not interchangeable and > conversion requires using their literal types in the enum's valueOf(). > > > 2) The SQL queries do not contain the enum cast, causing Postgres to > reject it. For example: > > SELECT > user_identity.id, > user_identity.user_id, > user_identity.identity, > user_identity.identity_type > FROM user_identity > WHERE ( > user_identity.identity = '[email protected]' > AND user_identity.identity_type = 'email' > ); > > This fails with the error: > PSQLException: ERROR: operator does not exist: identity_type = character > varying > > In the previous code I performed the cast manually which adds some bloat > that I was hoping to remove: > > private static final DataType<Object> IDENTITY_TYPE = > new DefaultDataType<>(POSTGRES, SQLDataType.OTHER, "identity_type"); > > UserIdentityRecord record = db.selectFrom(USER_IDENTITY) > .where(USER_IDENTITY.IDENTITY.eq((identity))) > .and(USER_IDENTITY.IDENTITY_TYPE.eq(identityType(identityType))) > .fetchOne(); > > private Field<String> identityType(String type) { > return DSL.coerce(DSL.cast(type, IDENTITY_TYPE), > USER_IDENTITY.IDENTITY_TYPE); > } > > Is there a way to use generated H2 enums with Postgres at runtime? > > Thanks! > Ben > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
