Dealing with non-primitive types with Talend tPostgresqlOuput component

Batch entry 0 INSERT INTO "public"."log" ("source","logged") VALUES ('123.45.56.78','2017-06-21 13:52:23') was aborted. Call getNextException to see the cause.

Ever bump into this kind of exception? This issue drove me crazy, and it took me a good few hours to figure out how to deal with it. The solution is actually very simple… once you know it!

To find out what can lead to this type of errors, let’s consider a very simple Talend job that consists of reading a CSV file and inserting each line into a table. The CSV file looks like this:

123.45.56.78;2017-06-21 13:52:23
78.90.123.45;2017-06-21 13:52:48
67.8.9.12;2017-06-21 13:52:51
...

The first attribute is a String representing an IP address, and the second is a Date (format “yyyy-MM-dd hh:mm:ss”). The log table looks like:

Notice the source attribute’s type is inet, a non-primitive type. Finally, the Talend job looks like this:

Its execution raises the exception presented at the top of this page.

First thing you do is to execute the SQL query. And… it works! So what’s the problem? Well, as you may guess, the problem comes from the source attribute which has a non-primitive type. To fix it, you’ll have to explicitly cast the values. To do so, open the Advanced settings panel of the tPostgresqlOutput component and add an Additional columns, and configure it as follows: 

And voilà!

The problem considered in this post appears every time an attribute type is not proposed by Talend (e.g., cidr, json, xml and circle). Fortunately, the solution presented here always applies.