PSQLException: ERROR: UNION types text and boolean cannot be matched

Recently I have faced the issue mentioned below

Caused by: org.postgresql.util.PSQLException: ERROR: UNION types text and boolean cannot be matched

Detailed stack trace :

org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)

at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:677)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:616)

... 81 common frames omitted

Use case : In my Repository, i have union of 3 queries and i have introduced new boolean attribute and i started getting this error

Important thing to note when using union : order of attributes or column should be same in all the individual queries.

Example Query which was giving above error 

select name,id isValid from ((select t1.name as name ,t1.id as id ,t1.isValid as isValid from table t1) union (select t2.name as name,t2.id as id,' ' as isValid  from table t2) union(select t3.name as name,t3.id,t3.isValid as isValid from table t3) ) table t;

Even though order is same for all the 3 querues, mistke in the above query is ' ' as isValid from table 2

I was assigning text type value to boolean attribute hence i was getting the exception

 correct way is ( pass default value in my case FALSE)

select name,id isValid from ((select t1.name as name ,t1.id as id ,t1.isValid as isValid from table t1) union (select t2.name as name,t2.id as id, FALSE as isValid  from table t2) union(select t3.name as name,t3.id,t3.isValid as isValid from table t3) ) table t;

Hope it helps :)