ERROR: column "boolean_val" is of type smallint but expression is of type boolean

[AlexandreP]

Hi,

We have issues regarding a migration from Oracle to Postgres 9.6, using Confluence 6.15.2

Confluence start correctly and all spaces/pages are there, but there are some functionality not working,... like impossible to "save pages".

It look like a CAST problem, but not sure how to resolve it.

Thank you for your help

2021-02-17 11:06:09,567 ERROR [http-nio-8080-exec-6] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: column "boolean_val" is of type smallint but expression is of type boolean
Hint: You will need to rewrite or cast the expression.
Position: 165
-- referer: http://epsvllq045.ep.parl.union.eu/wiki/ | url: /wiki/rest/dashboardmacros/1.0/updates | traceId: 098f827c7caf2e22 | userName: admin
2021-02-17 11:06:09,583 ERROR [http-nio-8080-exec-6] [org.hibernate.internal.ExceptionMapperStandardImpl] mapManagedFlushFailure HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: could not execute statement]
-- referer: http://epsvllq045.ep.parl.union.eu/wiki/ | url: /wiki/rest/dashboardmacros/1.0/updates | traceId: 098f827c7caf2e22 | userName: admin
2021-02-17 11:06:09,751 WARN [http-nio-8080-exec-6] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doCommit Commit failed. Rolling back. Error: Hibernate operation: could not execute statement; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "boolean_val" is of type smallint but expression is of type boolean
Hint: You will need to rewrite or cast the expression.
Position: 165
-- referer: http://epsvllq045.ep.parl.union.eu/wiki/ | url: /wiki/rest/dashboardmacros/1.0/updates | traceId: 098f827c7caf2e22 | userName: admin
2021-02-17 11:06:09,761 WARN [http-nio-8080-exec-6] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
->[bucket.user.persistence.dao.hibernate.BucketPropertySetDAO.setImpl]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #245889879)
-- referer: http://epsvllq045.ep.parl.union.eu/wiki/ | url: /wiki/rest/dashboardmacros/1.0/updates | traceId: 098f827c7caf2e22 | userName: admin
2021-02-17 11:06:09,786 ERROR [http-nio-8080-exec-6] [rest.api.model.ExceptionConverter] convertServiceException No status code found for exception, converting to internal server error :
-- referer: http://epsvllq045.ep.parl.union.eu/wiki/ | url: /wiki/rest/dashboardmacros/1.0/updates | traceId: 098f827c7caf2e22 | userName: admin
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute statement; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "boolean_val" is of type smallint but expression is of type boolean
Hint: You will need to rewrite or cast the expression.
Position: 165
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:619)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:605)
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:518)
at com.atlassian.confluence.impl.hibernate.ConfluenceHibernateTransactionManager.doCommit(ConfluenceHibernateTransactionManager.java:69)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:765)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:734)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy54.commit(Unknown Source)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:518)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy94.setImpl(Unknown Source)

[Danyal Iqbal · Accepted solution]

Hi,

 the error mesasge is a dream come true:

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

Postgresql cannot autocast. You can write your own cast function:

https://www.postgresql.org/docs/9.4/sql-createcast.html

[Danyal Iqbal]

a quick search on stackoverflow returned this result. I would start here:

https://stackoverflow.com/questions/31343809/casting-smallint-to-boolean-in-postgresql

CREATE OR REPLACE FUNCTION boolean1(i smallint) RETURNS boolean AS $$
    BEGIN
            RETURN (i::smallint)::int::bool;
    END;
$$ LANGUAGE plpgsql;

CREATE CAST (smallint AS boolean) WITH FUNCTION boolean1(smallint) AS ASSIGNMENT;