topic Re: Inserting into PostgreSQL ARRAY type in Talend Studio

Inserting into PostgreSQL ARRAY type

re_weavers — Wed, 08 Oct 2014 16:35:37 GMT

Hi All,
We have a table containing a PostgreSQL integer[]; which we need to insert into.
With tAggregate/tMap we can create a List<Integer> however when we try to use tPostgresqlOutput we get:

Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

I'm now converting it into a string of the format '{1,2,3}' (this is an array literal in PG) with a tMap and attempting to use that in-place of the array, however we get the following error from the tPostgresqlOutput:

ERROR: column "matrix" is of type integer[] but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

Does anyone have any idea how we can get this to work?!
Thanks in advance
-Rob

Re: Inserting into PostgreSQL ARRAY type

Anonymous — Fri, 31 Oct 2014 04:15:44 GMT

Hi,
Please use tPostgresqlRow instead of using tPostgresqlOutput to execute an insert query to insert data into PostgreSQL.
Let us know if it is OK with you.
Best regards
Sabrina

Re: Inserting into PostgreSQL ARRAY type

re_weavers — Thu, 20 Nov 2014 14:21:42 GMT

Hi,
Please use tPostgresqlRow instead of using tPostgresqlOutput to execute an insert query to insert data into PostgreSQL.
Let us know if it is OK with you.
Best regards
Sabrina

Hey Sabrina,
That works, however we've found it be non-performant over WAN links when compared to tPostgresqlOutput as it seems to have an effective batch size of 1; is there a way of applying a batch size to this?
Many Thanks,
Rob

Re: Inserting into PostgreSQL ARRAY type

re_weavers — Tue, 25 Nov 2014 12:16:18 GMT

For anyone actually trying to do this; we did the following in the end:
Originally we had a tMap and tPostgresRow; and assembled the INSERT query in the tMap then flowToIterate to the tPostgresRow and used that as the query. This turned out to be very slow over some of our VPN links as the number of rows/sec is limited by the latency between the job and the server as it has to been round-tripping.
So we did the following instead:
In the mapper we replace .toString() on the ArrayList is basically JSON object formatted ',]'; so we hack the string into Postgres format '{{1,2},{3,4}}'
Then in the tPostgresqlOutput we typecast the string to the correct datatype using the advanced options (see screenies):

Re: Inserting into PostgreSQL ARRAY type

_AnonymousUser — Mon, 19 Oct 2015 09:15:39 GMT

Hi Rob,
This turned out to be a very useful solution for us.We have been trying to eliminate the tpostgresqlrow component and have been unsuccessful.We tried the solution suggested by you and it worked out perfectly.
Thanks..
Sunil

Re: Inserting into PostgreSQL ARRAY type

Anonymous — Wed, 03 Jul 2019 23:36:04 GMT

Thank you sir! This worked like a charm.