Can Postgres silently ignore column constraint conflicts?

Question

I have a Postgres 9.6 table with certain columns that must be unique. If I try to insert a duplicate row, I want Postgres to simply ignore the insert and continue, instead of failing or aborting. If the insert is wrapped in a transaction, it shouldn't abort the transaction or affect other updates in the transaction.

I assume there's a way to create the table as described above, but I haven't figured it out yet.

Bonus points if you can show me how to do it in Rails.

Answer 1

This is possible with the ON CONFLICT clause for INSERT:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.

This is a relatively new feature and only available since Postgres 9.5, but that isn't an issue for you.

This is not something you specific at table creation, you'll need to modify each insert. I don't know how this works with Rails, but I guess you'll have to manually write at least part of the queries to do this.

This feature is also often called UPSERT, which is probably a better term to search for if you want to look for an integrated way in Rails to do this.