How to use RETURNING with ON CONFLICT in PostgreSQL?

sql

upsert

concurrency-management

performance-optimization

byNikita Barsukov·Dec 6, 2024

Execute an INSERT with an ON CONFLICT update and capture the result with RETURNING:

/* Starting off strong, just like my morning coffee!*/
INSERT INTO table_name (id, col1) VALUES (1, 'A')
/* And...Conflict resolved! You shall not pass, Gandalf would be proud!*/
ON CONFLICT (id) DO UPDATE SET col1 = EXCLUDED.col1
/* Give me all the data! I promise, I won't keep it all to myself!*/
RETURNING *;

Here, RETURNING * discloses the row after insert or update operation. EXCLUDED holds the values the row intended to have after the insert attempt.

Mastering UPSERT

Efficient Updates with ON CONFLICT DO UPDATE

Leverage ON CONFLICT DO UPDATE for efficient update management and to dodge unsolicited side effects. Well-defined constraints are crucial to accurately identify conflicts.

Concurrency Management & Strategic Locking

High concurrency calls for strategic concurrency management. Common Table Expressions (CTEs) and locking strategies fend off unnecessary updates and help maintain a streamlined data transaction flow.

/*Who runs the world? CTEs! */
WITH inserted AS (
  INSERT INTO my_table (id, data)
  VALUES (1, 'NewData')
  ON CONFLICT (id) DO UPDATE SET data = 'UpdatedData'
  RETURNING id
)
/*Let's have a peek at what we got!*/
SELECT * FROM inserted;

The RETURNING clause serves up the id which is inserted or updated, eliminating side effects on recurring calls.

COALESCE and UNION: Your New Best Friends

COALESCE and UNION offer creative solutions to tricky situations. Manage IDs and combine results of diverse functionalities like a pro!

/*Going up! Oh, that's already taken? OK, let's move along...*/
INSERT INTO my_table (id, data)
VALUES (2, 'Recorded')
ON CONFLICT (id) DO NOTHING
RETURNING id;
/*If COALESCE was in Avengers, it would be Hawkeye. Never misses!*/
SELECT COALESCE (
  (SELECT id FROM my_table WHERE id = 2),
  (SELECT id FROM inserted)
) AS id;

UPSERT Nuances and Advanced Tactics

Handling Performance Implications

Remember, performance is key. A wild RETURNING clause, especially RETURNING *, risks performance overhead on large tables. Always evaluate and tweak your queries for optimal performance.

Features: The Newer, The Better

Are your UPSERT operations and conflicts stacking up? Time to check your PostgreSQL version and upgrade to better conflict resolution features.

Defining Deadlocks and Dictionary Terms

Keep your row insertion order consistent for deadlock prevention. And avoid going the Shakespeare way - steer clear of reserved database words.

Simplify Frequent UPSERTs

Frequent conflicts making your life hard? Simplify and optimize by doing separate inserts and updates. Sure, it's not a one-liner, but the handling and retry process might thank you!

Wrapping it Up

Integrate these strategies to enhance your PostgreSQL UPSERT operations. Plan for concurrency, preempt deadlocks, heroically optimize performance, all while ensuring comprehensive error handling and re-run provisions for UPSERT failures.

explain-codes / Sql / How to use RETURNING with ON CONFLICT in PostgreSQL?

Linked

Update a Table with Data from Another Table



Mysql, update multiple tables with one query



Bulk insert, update if on conflict (bulk upsert) on Postgres



T-sql: Using a CASE in an UPDATE statement to update certain columns depending on a condition



Replace Into Query Syntax

