PostgreSQL Upsert: ON CONFLICT

Tip: Basic Upsert

INSERT INTO posts (slug, title) VALUES ('hello', 'Hello World')
ON CONFLICT (slug) DO UPDATE SET title = EXCLUDED.title;

Gotcha: EXCLUDED Pseudo-Table

EXCLUDED contains the values that would have been inserted.

Tip: DO NOTHING

INSERT INTO posts (slug, title) VALUES ('hello', 'Hello')
ON CONFLICT (slug) DO NOTHING;

Silently skips duplicates.

Gotcha: Conflict Target Required

You must specify which constraint to check: ON CONFLICT (slug) or ON CONFLICT ON CONSTRAINT constraint_name.

Tip: Conditional Update

ON CONFLICT (slug) DO UPDATE
SET title = EXCLUDED.title
WHERE posts.updated_at < EXCLUDED.updated_at;

Only update if the new data is newer.

Gotcha: Bulk Upsert

INSERT INTO posts (slug, title) VALUES ('a', 'A'), ('b', 'B')
ON CONFLICT (slug) DO UPDATE SET title = EXCLUDED.title;

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL's ON CONFLICT DO UPDATE (upsert) is a game-changer for bulk synchronization. I've replaced elaborate 'check if exists, then insert or update' application code with a single INSERT ... ON CONFLICT DO UPDATE statement. The key details: you need a unique constraint or index for the conflict target, and the excluded row statement (EXCLUDED.column_name) accesses the conflicting values. This is one feature where raw SQL outperforms Eloquent.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)