Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-upsert/ below:

PostgreSQL - Upsert - GeeksforGeeks

Last Updated : 23 Jul, 2025

UPSERT in PostgreSQL is a powerful database operation that merges the functionalities of INSERT and UPDATE into a single command. This operation allows users to either insert a new row into a table or update an existing row if it already exists.

Also, making it essential for efficient data management in PostgreSQL database systems. In this article, We will learn about the Upsert in PostgreSQL by understanding various examples in detail and so on.

• UPSERT in PostgreSQL is a database operation that combines the functionality of INSERT and UPDATE.
  
• It allows us to either insert a new row into a table or update an existing row if it already exists, all in a single command.
  
• This is particularly useful for maintaining data integrity and avoiding duplicate entries.

Syntax:

The basic syntax for the 'INSERT ON CONFLICT' statements is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, ...;

Explanation:

If we analyze the above syntax:

• table_name: The name of the table into which you want to insert data.
  
• (column1, column2, ...): The columns for which you want to insert values.
  
• VALUES(value1, value2, ...): The values you want to insert.
  
• ON CONFLICT (conflict_column): The column(s) that may cause a conflict.
  
• DO NOTHING: Skip the insert if a conflict occurs.
  
• DO UPDATE: Update the existing row if a conflict occurs.
  
• SET column = value1: Specify which columns to update.

1. Conflict Detection: The ON CONFLICT clause is used to specify which column(s) may cause a conflict during the insert operation. Typically, this would be a unique constraint or unique index.
   
2. Conflict Resolution: If a conflict is detected, you can either:
   • Use DO NOTHING to skip the insert if a conflict occurs, or
     
   • Use DO UPDATE to update the existing row with new values based on the conflict.
     

3. EXCLUDED Keyword: This keyword allows you to reference the values that you attempted to insert during the update operation, providing flexibility in how you modify existing records.

Let's create an inventory table and insert some initial data:

CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL
);
INSERT INTO inventory (name, price, quantity)

VALUES
    ('Item A', 10.99, 100),
    ('Item B', 15.49, 200),
    ('Item C', 20.00, 150)
RETURNING *;

Output:

Now, let's perform a UPSERT operation where we try to insert a new row or update an existing row if it already exists.

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'Item A', 12.99, 120)
ON CONFLICT (id)
DO UPDATE SET
    price = EXCLUDED.price,
    quantity = EXCLUDED.quantity;

Output:

INSERT 0 1

Here, we attempted to insert a new row with id = 1. Since this ID already exists, the operation updated the price and quantity for "Item A."

To verify the update, run the following query:

SELECT * FROM inventory WHERE id = 1;

Output:

Now, let’s insert a new item that doesn’t exist in the inventory:

INSERT INTO inventory (id, name, price, quantity)
VALUES (4, 'Item D', 25.99, 80)
ON CONFLICT (id)
DO UPDATE SET
    price = EXCLUDED.price,
    quantity = EXCLUDED.quantity;

Output:

INSERT 0 1

Run the following query to see all items in the inventory:

SELECT * FROM inventory ORDER BY id;

Output:

If we want to skip inserting a row that already exists, you can use DO NOTHING. Here’s an example:

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'Item A', 30.00, 100)
ON CONFLICT (id) DO NOTHING;

Output:

INSERT 0 0

This indicates that no rows were inserted because the ID already exists in the table.

In conclusion, the 'INSERT ON CONFLICT' statement in PostgreSQL is a versatile tool that simplifies handling scenarios where a row needs to be inserted or updated based on its existence. By mastering UPSERT, you can streamline your database operations and ensure data integrity efficiently. By understanding these aspects, you can effectively utilize UPSERT in your PostgreSQL projects and enhance your database management skills.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4