Inserting into PostgreSQL ARRAY type

One Star

Inserting into PostgreSQL ARRAY type

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

Accepted Solutions
One Star

Re: Inserting into PostgreSQL ARRAY type

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

All Replies
Moderator

Re: Inserting into PostgreSQL ARRAY type

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Inserting into PostgreSQL ARRAY type

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
One Star

Re: Inserting into PostgreSQL ARRAY type

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

Re: Inserting into PostgreSQL ARRAY type

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