How do I get PostgreSQL to keep inserting after unique index violation? I have ETL (Pentaho DI 8.2) to fill a Data Vault Hub table. Basically the ETL depends on the UNIQUE index to refuse duplicates of the business key column. In SQL Server this works great. With PostgreSQL I notice that it stops inserting after the first violation of the unique index. The ETL write is set to do individual inserts and ignore database errors. I add a rule to ignore the duplicates, but this slows down performance. Also I want to prevent this complication with an extra construct.

How do I get PostgreSQL to continue inserting valid rows into my hub tables?
My first suggestion would be to try avoiding duplicates in the first place: Pentaho has "Unique Rows" and "Unique Rows (hashset)" steps to help with that.
First option would work fast but is only good for pre-sorted input.

The table output step with error handling should be capable of proceeding correctly past the error. Could you check if you have any steps past the output? (E.g., "abort on error" would kill the transform). Posting a log would also help a lot to identify the issue.

UPDATE:
In short, due to PostgreSQL internals, the default behaviour on error within transaction is different to some other DBs. It requires a formal rollback or commit before any other commands could be issued. This behaviour made it's way into the JDBC driver used by Pentaho -- as expected.

SOLUTION:
Update the JDBC driver to the later version (at least 9.4.1210, but I would recommend version 42+). Then in the connection properties in Pentaho go to Advanced and create property "autosave" with value "always". This will effectively ROLLBACK any failing statements by automatically creating a savepoint after every successful statement. For more details see: https://jdbc.postgresql.org/documentation/head/connect.html

This comes at a minor performance hit, but likely not huge. You still have to keep the Commit size at "1" otherwise the whole batch will happily fail and you'll miss data.