How to write Enum into a PostGres DB using JOOQ

Discussion:

g***@gmail.com

2018-10-01 19:43:56 UTC

Hi,

I am trying to insert a row into the table using the query below using JOOQ.

create.insertInto(
table("stack_info"),
fieldByName("id"),
fieldByName("user_name"),
fieldByName("time_created"),
fieldByName("product"),
fieldByName("label"),
fieldByName("instance_type"),
fieldByName("status"))
.values(
request.getStackId(),
request.getUserName(),
request.getTimeCreated(),
request.getProduct().toString(),
request.getLabel(),
request.getInstanceType(),
request.getStatus().toString()
).execute();

Running the above query, I get the following error.

Caused by: org.jooq.exception.DataAccessException: SQL [insert into
stack_info ("id", "user_name", "time_created", "product", "label",
"instance_type", "status") values (?, ?, cast(? as timestamp), ?, ?, ?,
?)]; ERROR: column "status" is of type stack_status but expression is of
type character varying

Hint: You will need to rewrite or cast the expression.

Position: 163

stack_status is defined as an ENUM in the DB. It has the following values:-

stack_status :
PENDING,CREATE_COMPLETE,DELETE_IN_PROGRESS,DELETED,DELETE_FAILED

Any pointers, how to fix this ?
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 email to jooq-user+***@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

g***@gmail.com

2018-10-02 04:26:49 UTC

Permalink

I upgraded JOOQ to version 3.11. And used the following code.

create.insertInto(table("stack_info"))
.set(field(name("id")), request.getStackId())
.set(field(name("user_name")), request.getUserName())
.set(field(name("time_created")), request.getTimeCreated())
.set(field(name("product")), request.getProduct().toString())
.set(field(name("label")), request.getLabel())
.set(field(name("instance_type")), request.getInstanceType())
.set(field(name("status")), request.getStatus())
.execute();

I still get the following error:-

Caused by: org.postgresql.util.PSQLException: ERROR: column "status" is of
type stack_status but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

Position: 163

Can someone please help.

Thanks,
Ganesh

Post by g***@gmail.com
Hi,
I am trying to insert a row into the table using the query below using JOOQ.
create.insertInto(
table("stack_info"),
fieldByName("id"),
fieldByName("user_name"),
fieldByName("time_created"),
fieldByName("product"),
fieldByName("label"),
fieldByName("instance_type"),
fieldByName("status"))
.values(
request.getStackId(),
request.getUserName(),
request.getTimeCreated(),
request.getProduct().toString(),
request.getLabel(),
request.getInstanceType(),
request.getStatus().toString()
).execute();
Running the above query, I get the following error.
Caused by: org.jooq.exception.DataAccessException: SQL [insert into
stack_info ("id", "user_name", "time_created", "product", "label",
"instance_type", "status") values (?, ?, cast(? as timestamp), ?, ?, ?,
?)]; ERROR: column "status" is of type stack_status but expression is of
type character varying
Hint: You will need to rewrite or cast the expression.
Position: 163
stack_status is defined as an ENUM in the DB. It has the following values:-
PENDING,CREATE_COMPLETE,DELETE_IN_PROGRESS,DELETED,DELETE_FAILED
Any pointers, how to fix this ?
Thanks,
Ganesh

Rob Sargent

2018-10-02 05:13:07 UTC

Permalink

What data type does request.getStatus() return? and are you using the jOOQ code generator?

Post by g***@gmail.com
I upgraded JOOQ to version 3.11. And used the following code.
create.insertInto(table("stack_info"))
.set(field(name("id")), request.getStackId())
.set(field(name("user_name")), request.getUserName())
.set(field(name("time_created")), request.getTimeCreated())
.set(field(name("product")), request.getProduct().toString())
.set(field(name("label")), request.getLabel())
.set(field(name("instance_type")), request.getInstanceType())
.set(field(name("status")), request.getStatus())
.execute();
I still get the following error:-
Caused by: org.postgresql.util.PSQLException: ERROR: column "status" is of type stack_status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 163
Can someone please help.
Thanks,
Ganesh
Hi,
I am trying to insert a row into the table using the query below using JOOQ.
create.insertInto(
table("stack_info"),
fieldByName("id"),
fieldByName("user_name"),
fieldByName("time_created"),
fieldByName("product"),
fieldByName("label"),
fieldByName("instance_type"),
fieldByName("status"))
.values(
request.getStackId(),
request.getUserName(),
request.getTimeCreated(),
request.getProduct().toString(),
request.getLabel(),
request.getInstanceType(),
request.getStatus().toString()
).execute();
Running the above query, I get the following error.
Caused by: org.jooq.exception.DataAccessException: SQL [insert into stack_info ("id", "user_name", "time_created", "product", "label", "instance_type", "status") values (?, ?, cast(? as timestamp), ?, ?, ?, ?)]; ERROR: column "status" is of type stack_status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 163
stack_status is defined as an ENUM in the DB. It has the following values:-
stack_status : PENDING,CREATE_COMPLETE,DELETE_IN_PROGRESS,DELETED,DELETE_FAILED
Any pointers, how to fix this ?
Thanks,
Ganesh
--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.

Ganesh

2018-10-02 05:35:06 UTC

Permalink

Thanks for replying... I am not using JOOQ code generator.

request.getStatus() returns State which is an Java enum and has the
following values.

PENDING,CREATE_COMPLETE,DELETE_IN_PROGRESS,DELETED,DELETE_FAILED

The following query doesnt work too

create.insertInto(table("stack_info"))
.set(field(name("id")), request.getStackId())
.set(field(name("user_name")), request.getUserName())
.set(field(name("time_created")), request.getTimeCreated())
.set(field(name("product")), request.getProduct().toString())
.set(field(name("label")), request.getLabel())
.set(field(name("instance_type")), request.getInstanceType())
.set(field(name("status")), request.getStatus().toString())
.execute();

However, I am able to insert rows in the DB using SQL workbench which
happily accepts String values for enum columns..

Thanks,
Ganesh

Post by Rob Sargent
What data type does request.getStatus() return? and are you using the jOOQ code generator?
I upgraded JOOQ to version 3.11. And used the following code.
create.insertInto(table("stack_info"))
.set(field(name("id")), request.getStackId())
.set(field(name("user_name")), request.getUserName())
.set(field(name("time_created")), request.getTimeCreated())
.set(field(name("product")), request.getProduct().toString())
.set(field(name("label")), request.getLabel())
.set(field(name("instance_type")), request.getInstanceType())
.set(field(name("status")), request.getStatus())
.execute();
I still get the following error:-
Caused by: org.postgresql.util.PSQLException: ERROR: column "status" is of
type stack_status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 163
Can someone please help.
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.
--
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/K3sT3F5mnM0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

Lukas Eder

2018-10-02 07:49:40 UTC

Permalink

Post by Ganesh
Thanks for replying... I am not using JOOQ code generator.

Why not? :-) It would help you so much with your SQL, specifically when
these vendor specific data types are involved. But of course, you can do
the code generator's work manually. I'll explain below.

Post by Ganesh
However, I am able to insert rows in the DB using SQL workbench which
happily accepts String values for enum columns..

That's because you're not using a bind variable in SQL workbench, but a
literal. And PostgreSQL can convert between string literals and user
defined types more easily than if you're using bind variables. You could,
of course, tell jOOQ to use a literal as well, e.g. by using
DSL.inline(request.getStatus()). Or, you use the code generator. Or, you
explicitly specify types on your table columns. In your case:

...set(field(name("status"),
SQLDataType.VARCHAR.asEnumDataType(StatusEnum.class)), request.getStatus())

Your StatusEnum will need to implement org.jooq.EnumType for this to work
correctly.

Or really. You could just use the code generator :)

I hope this helps.
Lukas

Ganesh

2018-10-02 09:49:58 UTC

Permalink

Hi Lukas,

Thanks for reply. I tried both of your suggestions as got the same error.
Can you please advice.

1) .......set(field(name("status"),
SQLDataType.VARCHAR.asEnumDataType(State.class)), request.getStatus())

2) ...... Object object = DSL.inline(request.getStatus();
......set(field(name("status")), object)
Error:-

org.jooq.exception.DataAccessException: SQL [insert into gvsr_stack_info
("id", "user_name", "time_created", "product", "label", "instance_type",
"status") values (?, ?, cast(? as timestamp), ?, ?, ?, ?)]; ERROR: column
"status" is of type stack_status but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

Position: 163

at org.jooq_3.11.4.POSTGRES.debug(Unknown Source) ~[?:?]

at org.jooq.impl.Tools.translate(Tools.java:2384) ~[jooq-3.11.4.jar:?]

at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811)
~[jooq-3.11.4.jar:?]

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
~[jooq-3.11.4.jar:?]

at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)
~[jooq-3.11.4.jar:?]

State class implements EnumType.

import org.jooq.EnumType;

public enum State implements EnumType {

*PENDING*("PENDING"),

*IN_PROGRESS*("IN_PROGRESS"),

*CREATE_COMPLETE*("CREATE_COMPLETE"),

*DELETE_IN_PROGRESS*("DELETE_IN_PROGRESS"),

*DELETED*("DELETED");

private final String literal;

private State(String literal) {

this.literal = literal;

}

@Override

public String getName() {

return "state";

}

@Override

public String getLiteral() {

return this.literal;

}

}

Thanks,

Ganesh

Post by Lukas Eder

Post by Ganesh
Thanks for replying... I am not using JOOQ code generator.

Post by Ganesh
However, I am able to insert rows in the DB using SQL workbench which
happily accepts String values for enum columns..

That's because you're not using a bind variable in SQL workbench, but a
literal. And PostgreSQL can convert between string literals and user
defined types more easily than if you're using bind variables. You could,
of course, tell jOOQ to use a literal as well, e.g. by using
DSL.inline(request.getStatus()). Or, you use the code generator. Or, you
...set(field(name("status"),
SQLDataType.VARCHAR.asEnumDataType(StatusEnum.class)), request.getStatus())
Your StatusEnum will need to implement org.jooq.EnumType for this to work
correctly.
Or really. You could just use the code generator :)
I hope this helps.
Lukas
--
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/K3sT3F5mnM0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

Lukas Eder

2018-10-02 10:03:37 UTC

Permalink

Hi Ganesh,

Are you sure your changes are applied correctly? Especially the latter (the
one using DSL.inline()) would result in the status being inlined into the
query rather than a bind variable being passed.

Thanks,
Lukas

Post by Ganesh
Hi Lukas,
Thanks for reply. I tried both of your suggestions as got the same error.
Can you please advice.
1) .......set(field(name("status"),
SQLDataType.VARCHAR.asEnumDataType(State.class)), request.getStatus())
2) ...... Object object = DSL.inline(request.getStatus();
......set(field(name("status")), object)
Error:-
org.jooq.exception.DataAccessException: SQL [insert into gvsr_stack_info
("id", "user_name", "time_created", "product", "label", "instance_type",
"status") values (?, ?, cast(? as timestamp), ?, ?, ?, ?)]; ERROR: column
"status" is of type stack_status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 163
at org.jooq_3.11.4.POSTGRES.debug(Unknown Source) ~[?:?]
at org.jooq.impl.Tools.translate(Tools.java:2384) ~[jooq-3.11.4.jar:?]
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811)
~[jooq-3.11.4.jar:?]
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
~[jooq-3.11.4.jar:?]
at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)
~[jooq-3.11.4.jar:?]
State class implements EnumType.
import org.jooq.EnumType;
public enum State implements EnumType {
*PENDING*("PENDING"),
*IN_PROGRESS*("IN_PROGRESS"),
*CREATE_COMPLETE*("CREATE_COMPLETE"),
*DELETE_IN_PROGRESS*("DELETE_IN_PROGRESS"),
*DELETED*("DELETED");
private final String literal;
private State(String literal) {
this.literal = literal;
}
@Override
public String getName() {
return "state";
}
@Override
public String getLiteral() {
return this.literal;
}
}
Thanks,
Ganesh

Post by Lukas Eder

Post by Ganesh
Thanks for replying... I am not using JOOQ code generator.

Post by Ganesh
However, I am able to insert rows in the DB using SQL workbench which
happily accepts String values for enum columns..

That's because you're not using a bind variable in SQL workbench, but a
literal. And PostgreSQL can convert between string literals and user
defined types more easily than if you're using bind variables. You could,
of course, tell jOOQ to use a literal as well, e.g. by using
DSL.inline(request.getStatus()). Or, you use the code generator. Or, you
...set(field(name("status"),
SQLDataType.VARCHAR.asEnumDataType(StatusEnum.class)), request.getStatus())
Your StatusEnum will need to implement org.jooq.EnumType for this to work
correctly.
Or really. You could just use the code generator :)
I hope this helps.
Lukas
--
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/K3sT3F5mnM0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

Ganesh

2018-10-02 10:22:58 UTC

Permalink

I think DSL.inline() has worked for me. I think the changes werent applied
correctly.

Thanks a lot for helping me.

regards,
Ganesh

Post by Lukas Eder
Hi Ganesh,
Are you sure your changes are applied correctly? Especially the latter
(the one using DSL.inline()) would result in the status being inlined into
the query rather than a bind variable being passed.
Thanks,
Lukas

Post by Lukas Eder

Post by Ganesh
Thanks for replying... I am not using JOOQ code generator.

Post by Ganesh
However, I am able to insert rows in the DB using SQL workbench which
happily accepts String values for enum columns..

That's because you're not using a bind variable in SQL workbench, but a
literal. And PostgreSQL can convert between string literals and user
defined types more easily than if you're using bind variables. You could,
of course, tell jOOQ to use a literal as well, e.g. by using
DSL.inline(request.getStatus()). Or, you use the code generator. Or, you
...set(field(name("status"),
SQLDataType.VARCHAR.asEnumDataType(StatusEnum.class)), request.getStatus())
Your StatusEnum will need to implement org.jooq.EnumType for this to
work correctly.
Or really. You could just use the code generator :)
I hope this helps.
Lukas
--
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/K3sT3F5mnM0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

--
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/K3sT3F5mnM0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.