Conditional INSERT with ON CONFLICT in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ON CONFLICT clause do in a PostgreSQL INSERT statement?

It tells PostgreSQL what to do if the insert would cause a conflict, like a duplicate key. You can choose to do nothing or update existing data instead.

beginner

How do you prevent inserting duplicate rows using ON CONFLICT?

Use ON CONFLICT DO NOTHING. This skips the insert if a conflict happens, so no duplicate row is added.

intermediate

Explain the syntax to update a row if a conflict occurs during INSERT.

Use ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.value. This updates the existing row instead of inserting a new one.

intermediate

Can you use a WHERE clause with ON CONFLICT DO UPDATE? What is it for?

Yes, you can add WHERE to DO UPDATE to update only when certain conditions are true. This lets you control when to change the existing row.

advanced

What happens if you omit the conflict target in ON CONFLICT?

PostgreSQL raises an error unless it can unambiguously infer the conflict target from the table's unique or primary key constraints.

What does ON CONFLICT DO NOTHING do?

A Updates the existing row on conflict

B Inserts the row only if no conflict occurs

C Deletes the conflicting row

D Raises an error on conflict

ON CONFLICT DO NOTHING skips the insert if a conflict happens, so no new row is added.

Which clause specifies the column to check for conflicts in ON CONFLICT?

A ON CONFLICT (column_name)

B CONFLICT TARGET

C WHERE

D DO UPDATE

You specify the conflict target column inside parentheses after ON CONFLICT.

What does ON CONFLICT DO UPDATE allow you to do?

A Update the existing row when a conflict occurs

B Rollback the transaction

C Delete the conflicting row

D Insert a new row ignoring conflicts

DO UPDATE lets you change the existing row instead of inserting a duplicate.

Can you add a WHERE clause to ON CONFLICT DO UPDATE?

A Only with DO NOTHING

B No, WHERE is not allowed

C Yes, to conditionally update rows

D Only outside the INSERT statement

WHERE lets you update only rows that meet certain conditions during conflict handling.

What happens if you omit the conflict target in ON CONFLICT?

A PostgreSQL handles all conflicts automatically

B The statement ignores conflicts

C The statement inserts duplicates

D An error is raised

PostgreSQL raises an error unless it can unambiguously infer the conflict target from unique constraints.

Describe how to use ON CONFLICT to insert a row only if it does not already exist.

Think about skipping insert on conflict.

Explain how ON CONFLICT DO UPDATE works and when you might use it.

Consider updating data instead of inserting duplicates.