INSERT ON CONFLICT (upsert) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the INSERT ON CONFLICT clause do in PostgreSQL?

It allows you to try inserting a new row but if a conflict occurs (like a duplicate key), you can specify an alternative action such as updating the existing row instead of failing.

beginner

What keyword is used with INSERT ON CONFLICT to update existing rows when a conflict happens?

The keyword DO UPDATE is used to update the existing row when a conflict occurs during an insert.

beginner

Explain the purpose of the ON CONFLICT (column_name) DO NOTHING clause.

It tells PostgreSQL to skip the insert if a conflict happens on the specified column, leaving the existing row unchanged and avoiding an error.

intermediate

In the statement INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;, what does EXCLUDED.name mean?

EXCLUDED.name refers to the value that was attempted to be inserted for the name column. It is used to update the existing row with the new value.

beginner

Why is INSERT ON CONFLICT called an "upsert" operation?

Because it combines "update" and "insert": it inserts a new row if no conflict exists, or updates the existing row if a conflict happens.

What happens if you use ON CONFLICT (id) DO NOTHING and a row with the same id already exists?

A The existing row is deleted.

B The existing row is updated.

C An error is thrown.

D The insert is skipped and no error occurs.

DO NOTHING skips the insert when a conflict happens, so no error and no change to existing row.

Which clause lets you update an existing row during an insert conflict?

A DO UPDATE

B DO NOTHING

C ON DUPLICATE KEY

D REPLACE

DO UPDATE specifies how to update the existing row when a conflict occurs.

In ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, what does EXCLUDED represent?

A The row that caused the conflict

B A temporary table

C The new values attempted to insert

D The existing row in the table

EXCLUDED contains the values that were attempted to be inserted but caused the conflict.

Can INSERT ON CONFLICT be used to insert multiple rows at once?

A No, only single-row inserts are supported.

B Yes, it works with multi-row inserts.

C Only if you use a transaction.

D Only with a trigger.

PostgreSQL supports multi-row inserts with ON CONFLICT for each row.

What is the main benefit of using INSERT ON CONFLICT?

A It prevents duplicate rows and allows updating existing data in one command.

B It speeds up SELECT queries.

C It automatically creates indexes.

D It deletes conflicting rows.

It helps avoid errors from duplicates and lets you update existing rows in a single statement.

Describe how the INSERT ON CONFLICT clause works in PostgreSQL and give an example use case.

Think about inserting data that might already be there.

Explain the difference between DO NOTHING and DO UPDATE in the context of INSERT ON CONFLICT.

One ignores conflict, the other fixes it.