I wrote a rather large blogpost about it, so even though I might get downvotes for links, read this.
The gist is that transactions do not help in here (at least by default), and while it is possible to write correct upsert, it’s actually pretty tricky.
More Related Contents:
- Refactor a PL/pgSQL function to return the output of various SELECT queries
- How to implement a many-to-many relationship in PostgreSQL?
- How to update selected rows with values from a CSV file in Postgres?
- Optimise PostgreSQL for fast testing
- Transpose latest rows per user to columns
- How to delete duplicate rows without unique identifier
- Best way to check for “empty or null value”
- Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
- PostgreSQL parameterized Order By / Limit in table function
- How can I send email from PostgreSQL trigger?
- SQL Between clause with strings columns
- Insert an image in postgresql database
- NOT NULL constraint over a set of columns
- Why do NULL values come first when ordering DESC in a PostgreSQL query?
- How do I alter the position of a column in a PostgreSQL database table?
- Query that ignore the spaces
- How to find a table having a specific column in postgresql
- Continuing a transaction after primary key violation error
- INSERT COMMAND :: ERROR: column “value” does not exist
- Use email address as primary key?
- Bulk/batch update/upsert in PostgreSQL
- SQL query to find record with ID not in another table
- How to show row numbers in PostgreSQL query?
- Postgres Error: More than one row returned by a subquery used as an expression
- Grant privileges for a particular database in PostgreSQL
- How to compare dates in datetime fields in Postgresql?
- PostgreSQL how to create a copy of a database or schema?
- Temporal database design, with a twist (live vs draft rows)
- PostgreSQL nested INSERTs / WITHs for foreign key insertions
- Postgres: convert single row to multiple rows (unpivot)