Timeline for SQL UPSERT in Postgres
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 3, 2020 at 14:16 | comment | added | cycero | Thank you, sir, I'll probably make those fields unique and go with the ON CONFLICT approach. Thanks for your help! | |
| Mar 3, 2020 at 14:15 | vote | accept | cycero | ||
| Mar 3, 2020 at 14:13 | comment | added | user1822 | @cycero: then remove the duplicates and create the unique index. I have added a solution that doesn't need such an index. But it is not safe for concurrent inserts. | |
| Mar 3, 2020 at 14:13 | history | edited | user1822 | CC BY-SA 4.0 |
added 794 characters in body
|
| Mar 3, 2020 at 14:10 | comment | added | cycero | So, the table contains duplicate entries because of an error which has been in the code for a while. What I need to do is INSERT if no match and UPDATE when there's a match, that's it. I do not need to only INSERT and grow the table for no reason. | |
| Mar 3, 2020 at 14:07 | comment | added | user1822 | You are contradicting yourself. If you don't want to make them unique, you don't need the UPDATE or INSERT to begin with - just INSERT all the time. Your question clearly indicates that you do want to make them unique. | |
| Mar 3, 2020 at 14:06 | comment | added | cycero | I don't actually need to make them unique, I want to INSERT when they don't exist and UPDATE the value if they do exist, even if there's more than 1 row affected. | |
| Mar 3, 2020 at 14:04 | comment | added | user1822 | @cycero: if you want to make sure you don't have duplicates you should have a unique index on those columns. There is no way you can ensure uniqueness without them - especially not in an environment with concurrent inserts/updates | |
| Mar 3, 2020 at 14:03 | comment | added | cycero | Thank you, but I do not have and can't have unique keys on those fields. | |
| Mar 3, 2020 at 14:00 | history | answered | user1822 | CC BY-SA 4.0 |