Inserting into PostgreSQL ARRAY type

[re_weavers]

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

[Anonymous]

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_weavers]

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_weavers · Accepted solution]

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):

[_AnonymousUser]

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

[Anonymous]

Thank you sir! This worked like a charm.