Pentaho: Cannot import Boolean Value to table with PostgreSQL Bulk Loader?

Question

I am currently trying to import some data (from a csv file) to a postgreSQL database. For this, I am using the CSV file input step to import the csv file into Kettle. Second, I am using the Modified Java Script Value step for altering some values and I am also adding a new column named VALID. This column should always be true. I added the column VALID to the fields in the lower half of the step window. My step looks like following:

To import the data from kettle to the PostgreSQL database table, I am using the PostgreSQL Bulk Loader (as there are millions of rows to import). This steps looks like following:

As you can see in this image, the table column name is valid and the stream field is VALID (which is coming from the Javascript Value step). Both boolean. Should be working. But instead, I am getting the following error message if I run the transformation:

2018/02/12 14:52:50 - PostgreSQL Bulk Loader.0 - Caused by: 
org.postgresql.util.PSQLException: ERROR: invalid input syntax for type 
boolean: "1.0"
Wobei: COPY adac_test, line 1, column valid: "1.0"

Any suggestions on how to fix this?

jacktrade jacktrade · Accepted Answer · 2018-02-15T11:00:32

cast "valid" as string. In postgres the boolean values are "kind of" strings, not really but boolean values on postgres can be inserted as string:

https://www.postgresql.org/docs/9.6/static/datatype-boolean.html

based on this documentation , this should work:

   var VALID = 't';

and select type string instead boolean below the code window

Pentaho: Cannot import Boolean Value to table with PostgreSQL Bulk Loader?

1 Answers