Tutorials › PostgreSQL Essentials › UPSERT (INSERT ON CONFLICT)

UPSERT (INSERT ON CONFLICT)

5 min read Quiz at the end

INSERT ON CONFLICT handles duplicate key violations gracefully. Use DO UPDATE SET to change existing data or DO NOTHING to skip duplicates. This atomic upsert is safe for concurrent use without race conditions.

UPSERT Pattern

-- Insert or update on conflict
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- Insert or ignore
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO NOTHING;

← Lateral Joins Next: Array Operations →

Topic Quiz · 5 questions

Test your understanding before moving on

1. What is an UPSERT?

💡 UPSERT inserts a row, or updates it if a conflict (duplicate key) occurs.

2. Which clause handles conflicts in PostgreSQL?

💡 PostgreSQL uses ON CONFLICT DO UPDATE or DO NOTHING.

3. What does EXCLUDED refer to?

💡 EXCLUDED refers to the row that was proposed for insertion but conflicted.

4. What does DO NOTHING do?

💡 ON CONFLICT DO NOTHING silently skips the insert if a conflict occurs.

5. What must exist for ON CONFLICT to work?

💡 ON CONFLICT requires a unique constraint or primary key to detect conflicts.

Quick Access

UPSERT (INSERT ON CONFLICT)

UPSERT Pattern

Test your understanding before moving on