MERGE/UPSERT solves a common problem: insert a row if it does not exist, update it if it does. Traditional approach requires two operations (try INSERT, catch error, UPDATE) which has race conditions and is slow. UPSERT provides atomic insert-or-update in single operation. Think of UPSERT as "save" operation - it does not matter if record exists or not, just save the data. Different databases use different syntax: PostgreSQL uses INSERT...ON CONFLICT, MySQL uses INSERT...ON DUPLICATE KEY UPDATE, SQL Server/Oracle use MERGE statement. All provide same functionality: atomic insert-or-update. UPSERT is idempotent - running same operation multiple times produces same result (critical for ETL pipelines that may retry on failure). Performance is 10x better than separate INSERT/UPDATE because it is single operation with no error handling overhead.

PostgreSQL INSERT...ON CONFLICT (UPSERT):

PostgreSQL provides INSERT...ON CONFLICT for UPSERT operations. Syntax:

INSERT INTO table (columns) VALUES (values)

ON CONFLICT (conflict_column) DO UPDATE SET column = value;

Example: Upsert user data

INSERT INTO users (user_id, username, email, updated_at)

VALUES (1, 'john_doe', 'john@example.com', NOW())

ON CONFLICT (user_id)

DO UPDATE SET

username = EXCLUDED.username,

email = EXCLUDED.email,

updated_at = NOW();

How it works:

1. Try to INSERT row with user_id = 1

2. If user_id = 1 already exists (conflict on unique constraint), execute DO UPDATE

3. EXCLUDED refers to the row that would have been inserted (proposed values)

4. Update existing row with values from EXCLUDED

5. Operation is atomic (no race conditions)

ON CONFLICT clause specifies which column(s) to check for conflicts. Must be:

- Primary key column

- Column with UNIQUE constraint

- Column(s) with UNIQUE index

DO UPDATE clause specifies what to do on conflict:

- DO UPDATE SET: Update existing row

- DO NOTHING: Ignore conflict (skip insert)

EXCLUDED keyword:

EXCLUDED refers to the row that would have been inserted. Use EXCLUDED.column to access proposed values.

Example: Conditional update

INSERT INTO products (product_id, name, price, stock)

VALUES (100, 'Widget', 29.99, 50)

ON CONFLICT (product_id)

DO UPDATE SET

name = EXCLUDED.name,

price = EXCLUDED.price,

stock = products.stock + EXCLUDED.stock -- Add to existing stock

WHERE products.updated_at < NOW() - INTERVAL '1 hour'; -- Only update if stale

WHERE clause in DO UPDATE:

You can add WHERE clause to DO UPDATE to conditionally update. If WHERE is false, no update happens (but no error either).

Multiple conflict columns: