PostgreSQL - Upsert

The PostgreSQL UPSERT operation is used to insert a new row into a table or update an existing row if a conflict occurs. It is implemented using the INSERT ... ON CONFLICT clause, which helps prevent duplicate records and simplifies insert-or-update operations.

• Prevent duplicate records based on PRIMARY KEY or UNIQUE constraints.
• Simplify data synchronization and bulk data operations.

Syntax

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...)
ON CONFLICT (conflict_column)
DO UPDATE
SET column_1 = EXCLUDED.column_1,
    column_2 = EXCLUDED.column_2;

Where:

• table_name: The table where data will be inserted or updated.
• conflict_column: A column with a PRIMARY KEY or UNIQUE constraint used to detect conflicts.
• EXCLUDED: Refers to the values that were attempted to be inserted.
• DO UPDATE: Updates the existing row if a conflict occurs.
• DO NOTHING: Skips the insert operation if a conflict occurs.

Working

Firstly, create an inventory table and insert some records into it.

Example 1: Update an Existing Row Using UPSERT

The following query attempts to insert a row with id = 1. Since the row already exists, PostgreSQL updates the existing record.

Query:

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

SELECT * FROM inventory
WHERE id = 1;

Output:

Example 2: Insert a New Row Using UPSERT

The following query inserts a new row because id = 4 does not exist.

Query:

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

SELECT * FROM inventory
WHERE id = 4;

Output:

Example 3: Skip Insert Using ON CONFLICT DO NOTHING

The following query skips the insert operation if a row with the same id already exists.

Query:

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 16.99, 120)
ON CONFLICT (id)
DO NOTHING;

SELECT * FROM inventory
WHERE id = 1;

Output:

Example 4: UPSERT Using ON CONFLICT ON CONSTRAINT

The following query uses the primary key constraint to detect conflicts.

Query:

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 18.99, 150)
ON CONFLICT ON CONSTRAINT inventory_pkey
DO UPDATE
SET
    price = EXCLUDED.price,
    quantity = EXCLUDED.quantity;

SELECT * FROM inventory
WHERE id = 1;

Output:

Example 5: UPSERT Using WHERE

The following query updates the existing row only if the current quantity is greater than 50.

Query:

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 19.99, 80)
ON CONFLICT (id)
DO UPDATE
SET price = EXCLUDED.price,
    quantity = EXCLUDED.quantity
WHERE inventory.quantity > 50;

SELECT * FROM inventory
WHERE id = 1;

Output: