Caused by: org.postgresql.util.PSQLException: ERROR: column "imagesync__c" is of type boolean but expression is of type character varying

Question

I am using WSO2 Report Mediator to insert records to postgreSql Table. facing issue like column "imagesync__c" is of type boolean but expression is of type character varying

FYI,

DataType for the column "imagesync__c" is bool and using DBeaver 6.1.2

<property name="syncFlag" scope="default" type="BOOLEAN" value="true"/>
<dbreport description="Insert into product2">
            <connection>
                <pool>
                    <dsName>jdbc/QRSagAppSyncDataSource</dsName>
                </pool>
            </connection>
            <statement>
                <sql><![CDATA[INSERT INTO vonbibra_dev.product2(productcode,db_lastmodifiedbyuser,imagesync__c,sourcename__c)VALUES(?, ?,?,?,?)]]></sql>
                <parameter type="VARCHAR" value="CarSales"/>
                <parameter type="VARCHAR" value="qrsintegration"/>
                **<parameter type="VARCHAR" expression="get-property('syncFlag')"/>**
                <parameter type="VARCHAR" value="CarSales"/>
            </statement>
  </dbreport>

I have tried some casting which are mentioned below for that particular column, unfortunately none of them is working.

Casting ways

<parameter value="select get-property('syncFlag')::boolean" type="VARCHAR"/>
<parameter value="CAST(get-property('syncFlag') as BOOLEAN)" type="VARCHAR"/>
<parameter type="VARCHAR" value="1"/>
<parameter type="VARCHAR" value="SELECT DECODE(get-property('syncFlag'), 'false', '0', 'true', '1' )::BOOLEAN"/>

References:

CAST Function

TypeCasting

Cast VARCHAR to BOOLEAN

score 0 · Accepted Answer · answered Mar 30 '20 at 09:40

I have Solved my Issue by CASTING in sql Query instead of Input parameter.

<property name="syncFlag" scope="default" type="BOOLEAN" value="true"/>
<dbreport description="Insert into product2">
            <connection>
                <pool>
                    <dsName>jdbc/QRSagAppSyncDataSource</dsName>
                </pool>
            </connection>
            <statement>
                <sql><![CDATA[INSERT INTO vonbibra_dev.product2(productcode,db_lastmodifiedbyuser,imagesync__c,sourcename__c)VALUES(?, ?,?,?::BOOLEAN,?)]]></sql>
                <parameter type="VARCHAR" value="CarSales"/>
                <parameter type="VARCHAR" value="qrsintegration"/>
                **<parameter type="VARCHAR" expression="get-property('syncFlag')"/>**
                <parameter type="VARCHAR" value="CarSales"/>
            </statement>
  </dbreport>

Caused by: org.postgresql.util.PSQLException: ERROR: column "imagesync__c" is of type boolean but expression is of type character varying

1 Answers1