Mange date insert / update due to Postgres Constaints Errors

[Narendra2]

Hi,

I use MongoDB to read input and push formatted data to Postgres tables. I have constraints set for my tables viz. unique, char length for varchar e.g. varchar (64), foreign key etc.

Currently, if an error occurs due to any of these set constraints, entire batch of input data is skipped from inserting / updating to table and the ETL job exits.

Is there a way to skip just the erroneous records and process the remaining input data to be inserted / updated.

[Anonymous]

Hi

Uncheck the 'Die on error' box on tPostgresqlOutput component, the component will ignore the error and continue to process remaining input data.

If you want to output these problematic data, uncheck 'the use batch size' box aslo in the advanced settings panel of tPostgresqlOutput component, then link reject flow from tPostGresqlOutput to a file or DB component.

...tPostgresqlOutput--reject--tFileOutputDelimited

Regards

Shong

[Narendra2]

Hi Shong,

Thank you for the reply.

I have the 'Die on error' option Unchecked. Still the issue persists.

To be more specific, My table column data type is varchar(256). One of the records I try to read from input has exceeded this character length limit.

On the console of Talend tool, error is being displayed, but no data is inserted in the PostgreSQL table.

caused by: ERROR: value too long for type character varying(256)

Also, if I do not use batch size option, the workflow takes a whole lot of time to complete say for even a thousand records on input.

[Anonymous]

If you uncheck the 'die on error' box, the component will capture the error and continue to process next record, the error is being displayed on the console, but it will prevent the job execution. Is this your expected behavior?

[jlolling]

NO, please do not suggest such things please! If you disable this option EVERY error will be ignored!

[jlolling]

In case of violations of unique constraints you can use the option "insert or update on duplicate keys".

[Narendra2]

My expected behavior is to bypass / skip the error record and update / insert the remaining record to the postgres table.

Yes, I have the option 'die on error' - unchecked.

As you had mentioned, error is getting displayed on console. But data insert / update for the non-error records is not happening.

[Narendra2]

Hi,

I am working with a community edition of Talend v 8.1. I do not see the options you have mentioned above. Attached snapshots. Please check.

 

 

Is there another way to handle this. What happens is, when errors are thrown by PostgreSQL database, the entire batch of incoming data records are not being processed because of a few error ones.

[jlolling]

Damn, this new options are only available in the newer versions of the enterprise studio.

Than the only thing you can do is Insert or update but this is slow.

[Narendra2]

Yeah, really slow. Taking about 5 min or so just for 100 records on my PC. Can we handle this particular scenario of PostgreSQL constraints error handling via code routines? Is that option possible.