Inserting into PostgreSQL ARRAY type

Highlighted
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):
Tags (2)

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):
Tags (2)
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
Five Stars

Re: Inserting into PostgreSQL ARRAY type

Thank you sir! This worked like a charm. 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch