Passing null Values in String Format from iBatis into an Integer value in Postgres DB

Question

I am transferring data from 1 table (MySQL) to another (Postgres) using iBatis. Table1(String Name, INTEGER Age, DATE dob, String Note) The code I am using to INSERT data through Ibatis is:

<insert id="insertData" parameterType="TransferBean" >
INSERT 
    INTO ${base.tableName}(${base.columns}) 
        VALUES(
                ${base.columnValueStr}
            )
</insert>

Where columns is the list of the columns, and columnValueStr is a list of the values being passed in a comma separated format.

The base query created by iBatis being passed to Postgres is :

INSERT INTO TABLE2(Name,Age,Dob,Note) VALUES("Ayush",NULL,"Sample")

However Postgres is throwing the following error:

column \"Age\" is of type smallint but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.

My guess is that Postgres is reading NULL as 'null'. I have tried passing 0 (which works), and '' (does not work) based on Column Name, but its not a generic and graceful solution.

Filtering is not possible according to the type of the column.

I need help to know if there is a workaround I can make at the query or even JAVA level which would pass the NULL as a proper null.

P.S. I have tried inserting null values into SmallInt from Postgres IDE, and that works fine.

SIDU · Accepted Answer · 2016-01-20 02:32:49Z

0

You need to check if a variable = NULL, replace it with text NULL, as the NULL might be rendered as BLANK

make sure it is

INSERT INTO TABLE2(Name,Age,Dob,Note) VALUES('Ayush',NULL,'Sample','')

not the following: (which is not working for SQL)

INSERT INTO TABLE2(Name,Age,Dob,Note) VALUES('Ayush',,'Sample',)

Since you are transferring data from MySQL to PostgreSQL, have a try: www.topnew.net/sidu/ which might be easier for your task. Simply expert from one database, and import to another. As SIDU supports both MySQL and PostgreSQL

answered Jan 20, 2016 at 2:32

SIDU

2,2781 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing null Values in String Format from iBatis into an Integer value in Postgres DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related