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 :)

Transaction Propagation in Spring, Spring Boot

What is Transaction Propagation ?

Propagation defines our business logic's boundary. Spring manages to start and pause a transaction based on our propagation setting.

In Spring Boot, we enable the transaction propagation using @Transactional annotation.

@Transactional : It Describes a transaction attribute on an individual method or on a class. 

We can set propagation, isolation, timeout, read-only and rollback conditions for our transaction using this annotation.

If annotation is applied at class level, then spring consider it for all the public methods, but if we applied at private or protected method then it ignores without an error.

Ex : 

  @Transactional(propagation = Propagation.REQUIRES_NEW)

  public void deleteExistingData() {

  }

Following are the settings for propagation

1.REQUIRED -  Default. Support a current transaction, create a new one if none exists.

2.SUPPORTS - Support a current transaction, execute non-transactionally if none exists.

3.MANDATORY - Support a current transaction, throw an exception if none exists. 

throw IllegalTransactionStateException

4.REQUIRES_NEW - Creates a new transaction, and suspend the current transaction if one exists.

5.NOT_SUPPORTED - Execute non transitionally, suspend the current transaction if one exists.

6.NEVER - Execute non-transitionally, throw an exception if a transaction exists.

7.NESTED - Execute within a nested transaction if a current transaction exists.