Скачать или смотреть How to Upsert into One PostgreSQL Table from Another Table: A Clear Guide

Learn how to effectively upsert data from one PostgreSQL table to another and avoid common pitfalls such as referencing errors.
---
This video is based on the question https://stackoverflow.com/q/66755302/ asked by the user 'Salim Fadhley' ( https://stackoverflow.com/u/1179137/ ) and on the answer https://stackoverflow.com/a/66756366/ provided by the user 'Jeremy' ( https://stackoverflow.com/u/895640/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Upserting into one Postgres table from another table?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Upsert Operation in PostgreSQL

When working with relational databases, there often arises a need to either insert new records or update existing records. This operation is commonly known as "upsert." In PostgreSQL, the INSERT ... ON CONFLICT statement allows you to handle this efficiently. In this guide, we'll tackle how to upsert data from one table into another table within PostgreSQL, focusing on common mistakes and providing step-by-step guidance.

The Problem: Upserting Data

Consider the scenario where you have two tables with identical structures. For instance, assume they both include columns named "A", "B", and "key". You want to insert records from a temporary table into a permanent table. If a record with the same key already exists in the target table, you want to update the existing record instead of duplicating it.

Here's what you tried:

You attempted an upsert operation using the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

However, you encountered an error stating:

[[See Video to Reveal this Text or Code Snippet]]

This error can be frustrating, particularly when you believe the syntax is correct. Fear not, as we’ll address why this happens and how to fix it.

The Solution: Using the EXCLUDED Keyword

The main issue here lies in how PostgreSQL handles the ON CONFLICT clause. Instead of referencing the temporary table directly in your DO UPDATE statement, you should use the EXCLUDED keyword. This keyword represents the row that would have been inserted had there been no conflict, allowing you to access its values.

Correct Syntax

Here’s the corrected SQL query you should use for your upsert operation:

[[See Video to Reveal this Text or Code Snippet]]

Why Use EXCLUDED?

Clarity: The EXCLUDED keyword clearly denotes that you are updating columns with values from the new incoming records rather than trying to reference the original temporary table.

Simplicity: By using EXCLUDED, you simplify your SQL query and avoid potential pitfalls of referencing incorrect table names.

A Best Practice: Explicitly Specify Column Names

While your initial tables may have identical structures, it's advisable to explicitly define the column names in both the INSERT and SELECT parts of your query. This is crucial for a few reasons:

Future-proofing: If the table structures ever change, you avoid inadvertently inserting or updating the wrong columns.

Readability: Your SQL statement is clearer and easier to understand when you specify what data you're actually inserting or updating.

Example of Specifying Column Names

Here’s an updated version of the query where column names are explicitly stated:

[[See Video to Reveal this Text or Code Snippet]]

This version ensures that you're inserting and updating only the fields you intend, making your intentions clear to both the database and anyone who reads your code.

Final Thoughts

Upserting in PostgreSQL, particularly when pulling data from one table to another, may seem complicated at first. However, with a proper understanding of the ON CONFLICT clause and the correct usage of the EXCLUDED keyword, you can efficiently manage your data without errors. Always remember to specify your columns, and you'll set yourself up for success in future data management tasks.

If you found this guide helpful, feel free to leave a comment or share your experiences with upserting in PostgreSQL!