Using cast(xx as numeric) without precision


I am trying to achieve two related things at once:

First I would like to be able to cast something to numeric/decimal but without any precision in HQL. Let’s say I am manipulating a parameter of type string in my query and I want to cast it as a number, but without involving any precision. If I use cast(:param as big_decimal) for example, I get the default precision of 38,2 (which I would prefer not to change, because other things are relying on it).

Second I would like to be able to use this HQL also as SQL with PostgreSQL, so I was hoping to manage to configure my Dialect by adding any jdbctype, javatype and/or customtype/usertype that would allow me to always write: cast(xxx as numeric) and have it be understood by Hibernate as the bare numeric type.

With Hibernate 5, I was able to achieve this by hacking around by basically adding my own custom jdbc type, mapped to numeric (without the ($p, $s) that we can find for the definition of Types.NUMERIC) and adding a StandardBasicTypeTemplate for BigDecimal and NumericJdbcType (where I would override the sql type to my custom type):

private void registerNumericWithoutPrecision(TypeContributions typeContributions) {
        // we define our own custom jdbc type to bridge between HQL and SQL
        var customTypeNumericNoPrecision = 12345;
        try {
            // this is just to ensure we are not overriding an existing type:
            // if this happens, then we can just choose a different value for the custom type.
            throw new IllegalStateException(getTypeName(customTypeNumericNoPrecision));
        } catch (MappingException e) {
            // this is expected: the mapping does not exist
        // we map it to PostgreSQL's numeric without any precision nor scale:
        // in the parent class, Types.NUMERIC is mapped to numeric($p, $s)
        // and this is not something we want when using bare "numeric" as a type in HQL
        registerColumnType(customTypeNumericNoPrecision, "numeric");
        // we map it to the name "numeric" in HQL and the type BigDecimal in Java
            new StandardBasicTypeTemplate<>(
                new NumericTypeDescriptor() {
                    public int getSqlType() {
                        return customTypeNumericNoPrecision;

Now with version 6.4, this does not work and I am struggling to find an alternative way of achieving this. Even achieving the first objective seems to not be possible but I feel like I’m missing something obvious maybe.

Thanks for any help

You can register a custom DdlType into DdlTypeRegistry in a TypeContributor for the NUMERIC/DECIMAL type code. The custom DdlType can then override getCastTypeName to return just numeric if length and precision are null.

This would be a nice improvement for Hibernate ORM though, so if you could create a Jira improvement ticket in our issue tracker and possibly also provide a PR with the improvement, chances are good that we will accept this :slight_smile:

I would suggest you join us on Zulip though to discuss how this can be implemented.


Thank you for your help!

I tried adding such a DdlType:

    public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
            new DdlTypeImpl(SqlTypes.NUMERIC, "numeric", this)

I didn’t override getCastTypeName since its implementation already takes care of returning the value passed to the constructor when it does not contain $p or $s.

But I am still getting the following error:

Caused by: org.hibernate.HibernateException: unrecognized cast target type: numeric
	at org.hibernate.type.spi.TypeConfiguration.resolveCastTargetType(
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCastTarget(
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCastTarget(
	at org.hibernate.grammars.hql.HqlParser$CastTargetContext.accept(
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCastFunction(
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCastFunction(

According to the code it makes sense because it seems Hibernate is trying to find the type inside the basic type registry.

Once I am clearer about what I am trying to achieve (since there could be a lot of different ways of achieving the same objective), I will at least create a ticket yes :slight_smile:

You have to cast to BigDecimal

Ha indeed, but unfortunately my need is to be able to use cast(xx as numeric) in HQL because I want to be able to use the same string both for HQL and SQL.

I suspect that the solution to my problem will be to separate the execution paths for SQL and HQL. Once this is done, I can simply register a function in HQL to do any cast I want without hacking around (as proposed in Set precision in criteria BigInteger cast - #2 by beikov for example).

You will simply have to use two different strings. HQL is not SQL, nor a superset or anything.

Hehe, yes, I agree, I think I tried to be too clever for my own sake in this case.

Thank you for your help!

1 Like