Menu
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.
839
EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.
Introduction. In FME 2022.0, we have introduced the ability UPSERT updates to PostgreSQL and PostGIS databases. UPSERT is the ability to add a new row if it doesn't already exist, but also update the row if it does exist in a single action. In order to use UPSERT, your database will need a unique ID.
ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action. conflict_target can perform unique index inference.
From postgresql documentation: RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything.
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.
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
