Upsert in PostgreSQL

Upsert is the pattern of inserting a row if it does not exist, or updating it if it does — all in one atomic statement. PostgreSQL implements upsert through INSERT ... ON CONFLICT, introduced in version 9.5. There is no separate UPSERT keyword. When a conflict occurs on a unique constraint or unique index, you can either update the existing row using the proposed values (via DO UPDATE SET) or silently skip the row (DO NOTHING).

Syntax

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

To skip conflicting rows without updating:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (conflict_column)
DO NOTHING;

Practical Example

Create a product_inventory table that tracks stock levels, then upsert new stock data from a warehouse feed:

CREATE TABLE product_inventory (
  sku          VARCHAR(50) PRIMARY KEY,
  description  VARCHAR(200) NOT NULL,
  qty_on_hand  INT NOT NULL DEFAULT 0,
  unit_cost    NUMERIC(10, 2) NOT NULL,
  last_synced  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO product_inventory (sku, description, qty_on_hand, unit_cost)
VALUES
  ('WH-0021', 'Adjustable Monitor Arm',  45, 28.50),
  ('WH-0035', 'Cable Management Sleeve',  120, 4.99),
  ('WH-0047', 'Laptop Cooling Pad',       60, 19.75);

Upsert new stock data — some SKUs already exist, some are new:

INSERT INTO product_inventory (sku, description, qty_on_hand, unit_cost, last_synced)
VALUES
  ('WH-0021', 'Adjustable Monitor Arm',  52, 27.00, now()),  -- existing: update qty + cost
  ('WH-0047', 'Laptop Cooling Pad',       75, 19.75, now()), -- existing: update qty only
  ('WH-0063', 'USB-C Docking Station',   30, 55.00, now())   -- new: insert
ON CONFLICT (sku)
DO UPDATE SET
  qty_on_hand = EXCLUDED.qty_on_hand,
  unit_cost   = EXCLUDED.unit_cost,
  last_synced = EXCLUDED.last_synced;

Output:

INSERT 0 3

Verify the result:

SELECT sku, description, qty_on_hand, unit_cost
FROM product_inventory
ORDER BY sku;

   sku   |        description        | qty_on_hand | unit_cost
---------+---------------------------+-------------+-----------
 WH-0021 | Adjustable Monitor Arm    |          52 |     27.00
 WH-0035 | Cable Management Sleeve   |         120 |      4.99
 WH-0047 | Laptop Cooling Pad        |          75 |     19.75
 WH-0063 | USB-C Docking Station     |          30 |     55.00

ON CONFLICT Variants

Skip duplicate rows with DO NOTHING

INSERT INTO product_inventory (sku, description, qty_on_hand, unit_cost)
VALUES ('WH-0021', 'Adjustable Monitor Arm', 45, 28.50)
ON CONFLICT (sku)
DO NOTHING;

Returns INSERT 0 0 when the row already exists — no error, no update.

Conditional update with WHERE

Avoid no-op updates by only updating when the incoming value differs:

INSERT INTO product_inventory (sku, description, qty_on_hand, unit_cost, last_synced)
VALUES ('WH-0021', 'Adjustable Monitor Arm', 52, 27.00, now())
ON CONFLICT (sku)
DO UPDATE SET
  qty_on_hand = EXCLUDED.qty_on_hand,
  unit_cost   = EXCLUDED.unit_cost,
  last_synced = now()
WHERE product_inventory.qty_on_hand <> EXCLUDED.qty_on_hand
   OR product_inventory.unit_cost   <> EXCLUDED.unit_cost;

Conflict on a named constraint

For multi-column unique constraints, reference the constraint by name:

INSERT INTO pricing_tiers (region, tier_name, monthly_rate)
VALUES ('EMEA', 'Pro', 49.00)
ON CONFLICT ON CONSTRAINT pricing_tiers_region_tier_name_key
DO UPDATE SET monthly_rate = EXCLUDED.monthly_rate;

Testing with Vela

Upsert logic is often part of ETL pipelines or sync jobs that run repeatedly against production data. Use Vela database branching to replay your upsert workload against a branch of production data before deploying changes to the sync job itself. This lets you verify that the conflict target is correct, that EXCLUDED references the right columns, and that existing rows are updated as expected — without touching live data.

Production Tips

• Always specify an explicit conflict target — ON CONFLICT (column) or ON CONFLICT ON CONSTRAINT name — so the statement fails loudly if the schema changes and the constraint is gone.
• Add a WHERE clause to DO UPDATE to skip no-op updates. This reduces write amplification and avoids unnecessary WAL records.
• INSERT ... ON CONFLICT is atomic: the conflict check and the resulting action occur as a single operation.
• For PostgreSQL 15+, consider MERGE when you need a single statement that can also handle deletes based on source data.
• Validate upsert correctness under concurrent load using SERIALIZABLE isolation if multiple processes upsert the same keys simultaneously.
• Use RETURNING merge_action() is not available with INSERT ON CONFLICT — use RETURNING * and check the xmax system column to distinguish inserts from updates if needed.