Coding⏱️ 2 min read📅 2026-06-03

How to Fix: How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

PostgreSQL upsert solution using INSERT ... ON CONFLICT UPDATE

Quick Answer: Use the INSERT ... ON CONFLICT UPDATE statement to achieve an upsert in PostgreSQL, available from pg 9.5 onwards.

📋 Table of Contents

  1. 🛠️ Step-by-Step Verified Fixes
  2. 🎯 Final Words

To perform an upsert operation in PostgreSQL, you can use a combination of the INSERT and UPDATE statements. This approach works by first inserting a new record into the table if it doesn't exist, and then updating the existing record if it does.

🛠️ Step-by-Step Verified Fixes

Method 1: Using a Single Query with INSERT ... ON CONFLICT

  1. Step 1: Create a new record using the INSERT statement, and specify the ON CONFLICT clause to handle duplicate entries.

Example:

INSERT INTO testtable (id, somedata) VALUES (3, 'Ala') ON CONFLICT (id) DO UPDATE SET somedata = EXCLUDED.sodata;
  • Step 2: The PostgreSQL database will insert the new record if it doesn't exist, and update the existing record with the new data if it does.

    • Method 2: Using a Single Query with MERGE (available in PostgreSQL 9.5+)

    1. Step 1: Create a new record using the MERGE statement, and specify the ON CONFLICT clause to handle duplicate entries.

    Example:

    MERGE INTO testtable AS target USING (VALUES (2, 'Joe')) AS source ON target.id = source.id WHEN NOT MATCHED THEN INSERT (id, somedata) VALUES (source.id, source.sodata);
  • Step 2: The PostgreSQL database will insert the new record if it doesn't exist, and update the existing record with the new data if it does.

    • 🎯 Final Words

    By using either of these methods, you can perform an upsert operation in PostgreSQL and achieve the desired result.

    Did this fix your problem?

    If not, try searching for specific error codes.

    🔍 Search Error Database

    ❓ Frequently Asked Questions

    🛠️ Related Fixes

    How to Fix: Stuck in tutorial hell after 4 years: How do I b

    Fix Stuck in tutorial hell after 4 years: How do I bui. Practice build

    How to Fix: Trying to sync mutliple audio tracks to a movie

    Fix Trying to sync mutliple audio tracks to a movie bu. Consider using

    How to Fix: Failed to merge latest branches from upstream re

    Fix Failed to merge latest branches from upstream repo. Try running 'g