Cast column type for expression in Dialect for Postgres

Hibernate ORM
1

Hi,

I use Postgres DB and Hibernate 6.2.x.

I perform such SQL:
UPDATE MY_TABLE SET MY_BOOLEAN_FIELD = 0

And I got this error because MY_BOOLEAN_FIELD is boolean in DB:

ERROR: column "MY_BOOLEAN_FIELD" is of type boolean but expression is of type integer
  Hint: You will need to rewrite or cast the expression.

For me works adding this “cast” to Postgres pg_cast:
UPDATE pg_cast set castcontext ='i' where castsource=23 and casttarget=16;

23 = int4
16 = bool

The above statement for me sounds like:
For expressions cast type “int4” to type “bool”.

How can I do the same by extending the PostgreSQLDialect class?

I tried to override a couple of methods in PostgreSQLDialect but it did not help me.

JdbcType resolveSqlTypeDescriptor()

or

String columnType(int sqlTypeCode)

2

I’m not sure what you’re trying to achieve: you have an Integer typed field in your entity mapping that you wish to store in a boolean PostgreSQL field? If so, you can use an @AttributeConverter and Hibernate will handle the types for you automatically.

If you would please give a little bit more context as to what you’re trying to achieve within Hibernate functionalities, I can provide more information or suggestions.

3

I have a boolean type field in my DB - it is boolean for both Hibernate mapping and actual DB structure

In most cases, everything works fine.
But in some cases, we perform such kind of query:
UPDATE MY_TABLE SET MY_BOOLEAN_FIELD = 0

I suppose that query was implemented for MySQL where this
MY_BOOLEAN_FIELD = 0
statement automatically cast from integer 0 to a boolean False value.
But for Postgres this doesn’t happen ..

The question is how to cast an Integer value to Boolean only for expressions.
Is this possible by extending the PostgreSQLDialect class?

4

Here is the reference on how we can do this with internal Postgres functionality.
But I neet to solve it in Java.

5

You should just write UPDATE MY_TABLE SET MY_BOOLEAN_FIELD = false. This will work automatically on all supported databases, any needed conversion to a numeric type will be implicitly done by Hibernate.

6

Sure, I understand this. But this code is in the Framework that I use and for me it is difficult to override these SQL queries.

7

I’m sorry, but the “Framework” you’re using will have to issue correct HQL queries for the current Hibernate version.