PostgreSQL ENUM types throwing exception on insert using the generated DAO

Discussion:

Jonathan

2016-01-06 02:20:19 UTC

Hi,

I have a postgres table containing an ENUM type as follows:

CREATE TYPE notification_type AS ENUM ('TYPE_A', 'TYPE_B', 'TYPE_C');

CREATE TABLE users
(
id bigint NOT NULL,
notification_type notification_type,
CONSTRAINT users_pkey PRIMARY KEY (id)
);

The jooq code generator outputs the following EnumType for the above and
the generated POJOs reference this.

public enum NotificationType implements EnumType {

DEFAULT("DEFAULT"),

EMAIL("EMAIL"),

SMS("SMS");

private final String literal;

private NotificationType(String literal) {
this.literal = literal;
}

/**
* {@inheritDoc}
*/
@Override
public Schema getSchema() {
return Public.PUBLIC;
}

/**
* {@inheritDoc}
*/
@Override
public String getName() {
return "notification_type";
}

/**
* {@inheritDoc}
*/
@Override
public String getLiteral() {
return literal;
}
}

The issue I'm having is inserting using the generated DAOs insert(Users)
method. Calling insert results in the following stack trace:

SEVERE: [127.0.0.1]:5701 [rx8081] [3.5.4] SQL [insert into "public"."users"
("id", "username", "password_hash", "email", "display_name", "first_name",
"last_name", "phone", "birth_date", "notification_type", "pending_email")
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; ERROR: column
"notification_type" is of type notification_type but expression is of type
character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
org.jooq.exception.DataAccessException: SQL [insert into "public"."users"
("id", "username", "password_hash", "email", "display_name", "first_name",
"last_name", "phone", "birth_date", "notification_type", "pending_email")
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; ERROR: column
"notification_type" is of type notification_type but expression is of type
character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
at org.jooq.impl.Utils.translate(Utils.java:1690)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:660)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:177)
at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:143)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)
at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:139)
at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:132)
at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:127)
at org.jooq.impl.DAOImpl.insert(DAOImpl.java:161)
at org.jooq.impl.DAOImpl.insert(DAOImpl.java:140)
...
Caused by: org.postgresql.util.PSQLException: ERROR: column
"notification_type" is of type notification_type but expression is of type
character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:495)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:441)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at
org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
at com.sun.proxy.$Proxy35.executeUpdate(Unknown Source)
at
org.jooq.tools.jdbc.DefaultPreparedStatement.executeUpdate(DefaultPreparedStatement.java:88)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:397)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
... 31 more

Am I right in assuming that this should work? Any direction here would be
much appreciated.

--
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 jooq-user+***@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Lukas Eder

2016-01-06 08:28:29 UTC

Permalink

What jOOQ version are you using? There had been some bug fixes in this
area, recently... jOOQ should render a cast around the String bind variable
?::enum_type_name

Post by Jonathan
Hi,
CREATE TYPE notification_type AS ENUM ('TYPE_A', 'TYPE_B', 'TYPE_C');
CREATE TABLE users
(
id bigint NOT NULL,
notification_type notification_type,
CONSTRAINT users_pkey PRIMARY KEY (id)
);
The jooq code generator outputs the following EnumType for the above and
the generated POJOs reference this.
public enum NotificationType implements EnumType {
DEFAULT("DEFAULT"),
EMAIL("EMAIL"),
SMS("SMS");
private final String literal;
private NotificationType(String literal) {
this.literal = literal;
}
/**
*/
@Override
public Schema getSchema() {
return Public.PUBLIC;
}
/**
*/
@Override
public String getName() {
return "notification_type";
}
/**
*/
@Override
public String getLiteral() {
return literal;
}
}
The issue I'm having is inserting using the generated DAOs insert(Users)
SEVERE: [127.0.0.1]:5701 [rx8081] [3.5.4] SQL [insert into
"public"."users" ("id", "username", "password_hash", "email",
"display_name", "first_name", "last_name", "phone", "birth_date",
"notification_type", "pending_email") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)]; ERROR: column "notification_type" is of type notification_type but
expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
org.jooq.exception.DataAccessException: SQL [insert into "public"."users"
("id", "username", "password_hash", "email", "display_name", "first_name",
"last_name", "phone", "birth_date", "notification_type", "pending_email")
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; ERROR: column
"notification_type" is of type notification_type but expression is of type
character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
at org.jooq.impl.Utils.translate(Utils.java:1690)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:660)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:177)
at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:143)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)
at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:139)
at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:132)
at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:127)
at org.jooq.impl.DAOImpl.insert(DAOImpl.java:161)
at org.jooq.impl.DAOImpl.insert(DAOImpl.java:140)
...
Caused by: org.postgresql.util.PSQLException: ERROR: column
"notification_type" is of type notification_type but expression is of type
character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:495)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:441)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at
org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
at com.sun.proxy.$Proxy35.executeUpdate(Unknown Source)
at
org.jooq.tools.jdbc.DefaultPreparedStatement.executeUpdate(DefaultPreparedStatement.java:88)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:397)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
... 31 more
Am I right in assuming that this should work? Any direction here would be
much appreciated.
--
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
For more options, visit https://groups.google.com/d/optout.

g***@gmail.com

2018-10-02 01:56:15 UTC

Permalink

How did you fix this? I am getting the same error with Jooq = 3.0.0;

Thanks,
Ganesh

Lukas Eder

2018-10-02 07:32:41 UTC

Permalink

Hi Ganesh,

For starters... Please upgrade your jOOQ version and see if the problem
persists :-)

I hope this helps,
Lukas

Post by g***@gmail.com
How did you fix this? I am getting the same error with Jooq = 3.0.0;
Thanks,
Ganesh

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
For more options, visit https://groups.google.com/d/optout.

Ganesh

2018-10-02 07:50:16 UTC

Permalink

Hi Lukas,

Thanks for replying, I upgraded Jooq to 3.11 and still see the same issue.
May be I am doing something wrong.

The post https://groups.google.com/forum/#!topic/jooq-user/K3sT3F5mnM0 has
more details about my issue. Can you please help!

Thanks,
Ganesh

Post by Lukas Eder
Hi Ganesh,
For starters... Please upgrade your jOOQ version and see if the problem
persists :-)
I hope this helps,
Lukas

Post by g***@gmail.com
How did you fix this? I am getting the same error with Jooq = 3.0.0;
Thanks,
Ganesh

Post by Jonathan
Hi,
CREATE TYPE notification_type AS ENUM ('TYPE_A', 'TYPE_B', 'TYPE_C');
CREATE TABLE users
(
id bigint NOT NULL,
notification_type notification_type,
CONSTRAINT users_pkey PRIMARY KEY (id)
);
The jooq code generator outputs the following EnumType for the above and
the generated POJOs reference this.
public enum NotificationType implements EnumType {
DEFAULT("DEFAULT"),
EMAIL("EMAIL"),
SMS("SMS");
private final String literal;
private NotificationType(String literal) {
this.literal = literal;
}
/**
*/
@Override
public Schema getSchema() {
return Public.PUBLIC;
}
/**
*/
@Override
public String getName() {
return "notification_type";
}
/**
*/
@Override
public String getLiteral() {
return literal;
}
}
The issue I'm having is inserting using the generated DAOs insert(Users)
SEVERE: [127.0.0.1]:5701 [rx8081] [3.5.4] SQL [insert into
"public"."users" ("id", "username", "password_hash", "email",
"display_name", "first_name", "last_name", "phone", "birth_date",
"notification_type", "pending_email") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)]; ERROR: column "notification_type" is of type notification_type but
expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
org.jooq.exception.DataAccessException: SQL [insert into
"public"."users" ("id", "username", "password_hash", "email",
"display_name", "first_name", "last_name", "phone", "birth_date",
"notification_type", "pending_email") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)]; ERROR: column "notification_type" is of type notification_type but
expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
at org.jooq.impl.Utils.translate(Utils.java:1690)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:660)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:177)
at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:143)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)
at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:139)
at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:132)
at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:127)
at org.jooq.impl.DAOImpl.insert(DAOImpl.java:161)
at org.jooq.impl.DAOImpl.insert(DAOImpl.java:140)
...
Caused by: org.postgresql.util.PSQLException: ERROR: column
"notification_type" is of type notification_type but expression is of type
character varying
Hint: You will need to rewrite or cast the expression.
Position: 223
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:495)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:441)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at
org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
at com.sun.proxy.$Proxy35.executeUpdate(Unknown Source)
at
org.jooq.tools.jdbc.DefaultPreparedStatement.executeUpdate(DefaultPreparedStatement.java:88)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:397)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
... 31 more
Am I right in assuming that this should work? Any direction here would
be much appreciated.
--

--
You received this message because you are subscribed to a topic in the
Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/jooq-user/zky7GvsDSuQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.