# Cast column type for expression in Dialect for Postgres

**URL:** <https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253>\
**Category:** Hibernate ORM\
**Created:** [March 20, 2025, 12:10pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253 "2025-03-20T12:10:34Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![fedorovstas](https://avatars.discourse-cdn.com/v4/letter/f/a8b319/32.png) [@fedorovstas](https://discourse.hibernate.org/u/fedorovstas)\
**Post date:** [March 20, 2025, 12:10pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/1 "2025-03-20T12:10:34Z")

</div>

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:

```auto
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)`

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [March 20, 2025, 1:12pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/2 "2025-03-20T13:12:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fedorovstas](https://avatars.discourse-cdn.com/v4/letter/f/a8b319/32.png) [@fedorovstas](https://discourse.hibernate.org/u/fedorovstas)\
**Post date:** [March 20, 2025, 1:33pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/3 "2025-03-20T13:33:19Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![fedorovstas](https://avatars.discourse-cdn.com/v4/letter/f/a8b319/32.png) [@fedorovstas](https://discourse.hibernate.org/u/fedorovstas)\
**Post date:** [March 20, 2025, 1:55pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/4 "2025-03-20T13:55:05Z")

</div>

> **[51.10. pg\_cast](https://www.postgresql.org/docs/current/catalog-pg-cast.html)**
>
> 51.10. pg\_cast # The catalog pg\_cast stores data type conversion paths, both built-in and user-defined. It should be noted that pg\_cast …

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

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [March 20, 2025, 2:06pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/5 "2025-03-20T14:06:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fedorovstas](https://avatars.discourse-cdn.com/v4/letter/f/a8b319/32.png) [@fedorovstas](https://discourse.hibernate.org/u/fedorovstas)\
**Post date:** [March 20, 2025, 2:10pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/6 "2025-03-20T14:10:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [March 20, 2025, 2:14pm UTC](https://discourse.hibernate.org/t/cast-column-type-for-expression-in-dialect-for-postgres/11253/7 "2025-03-20T14:14:12Z")

</div>

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