Upserts with INSERT … ON CONFLICT

An upsert means "insert this row, or update it if it already exists". Doing it with a SELECT followed by INSERT or UPDATE is racy — two sessions can both decide to insert. INSERT ... ON CONFLICT does it atomically, based on a unique constraint or primary key.

ON CONFLICT (columns) DO NOTHING skips duplicates. ON CONFLICT (columns) DO UPDATE SET col = EXCLUDED.col updates the existing row; EXCLUDED is the row you tried to insert. Add a WHERE to update only when it makes sense, such as keeping the highest score.

A common import pattern: load the spreadsheet into a staging table, then upsert from it into the real table in one statement. PostgreSQL 15+ also offers MERGE for more complex insert/update/delete logic.

upserts.sqlSQL

CREATE TABLE mock_exam_scores (
    student_id   bigint   NOT NULL REFERENCES students (id),
    subject_code text     NOT NULL,
    score        smallint NOT NULL CHECK (score BETWEEN 0 AND 100),
    updated_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (student_id, subject_code)
);

INSERT INTO mock_exam_scores (student_id, subject_code, score) VALUES
    (1, 'MATH', 80), (2, 'MATH', 45), (3, 'MATH', 70);

-- Re-sending the same rows would fail with a duplicate key... unless:
INSERT INTO mock_exam_scores (student_id, subject_code, score) VALUES (1, 'MATH', 80)
ON CONFLICT (student_id, subject_code) DO NOTHING;

-- Corrected marks arrive: update existing rows, insert new ones
INSERT INTO mock_exam_scores (student_id, subject_code, score) VALUES
    (1, 'MATH', 85),          -- exists: updated
    (4, 'MATH', 38)           -- new: inserted
ON CONFLICT (student_id, subject_code)
DO UPDATE SET score = EXCLUDED.score, updated_at = now()
RETURNING student_id, score, (xmax = 0) AS inserted;

-- Keep only the best attempt: update only when the new score is higher
INSERT INTO mock_exam_scores (student_id, subject_code, score) VALUES (2, 'MATH', 40), (3, 'MATH', 77)
ON CONFLICT (student_id, subject_code)
DO UPDATE SET score = EXCLUDED.score, updated_at = now()
WHERE EXCLUDED.score > mock_exam_scores.score;

SELECT student_id, subject_code, score FROM mock_exam_scores ORDER BY student_id;

-- Import pattern: staging table, then one upsert
CREATE TEMP TABLE scores_import (student_id bigint, subject_code text, score smallint);
INSERT INTO scores_import VALUES (5, 'MATH', 66), (1, 'MATH', 90), (6, 'MATH', 99);

INSERT INTO mock_exam_scores (student_id, subject_code, score)
SELECT student_id, subject_code, score FROM scores_import
ON CONFLICT (student_id, subject_code)
DO UPDATE SET score = EXCLUDED.score, updated_at = now();

SELECT count(*) AS rows_now FROM mock_exam_scores;   -- 6 (students 5 and 6 were new; 1 was updated)

Runs in your browser · PostgreSQL

Key points

ON CONFLICT makes insert-or-update atomic — no race between check and insert.
EXCLUDED refers to the row you tried to insert; add WHERE for conditional updates.
Import via a staging table plus one upsert; RETURNING (xmax = 0) tells inserts from updates.

Exercise

Create a daily_attendance_count (day date PRIMARY KEY, present int NOT NULL) table. Write an upsert that adds 1 to today's count each time it runs (inserting 1 the first time), run it three times, and confirm the count is 3.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The first run inserts the row with 1; every later run hits the primary-key conflict and adds 1 to the stored count. daily_attendance_count.present refers to the existing row's value inside DO UPDATE.

counter-upsert.sqlSQL

CREATE TABLE daily_attendance_count (
    day     date PRIMARY KEY,
    present int  NOT NULL
);

INSERT INTO daily_attendance_count (day, present) VALUES (current_date, 1)
ON CONFLICT (day) DO UPDATE SET present = daily_attendance_count.present + 1;

INSERT INTO daily_attendance_count (day, present) VALUES (current_date, 1)
ON CONFLICT (day) DO UPDATE SET present = daily_attendance_count.present + 1;

INSERT INTO daily_attendance_count (day, present) VALUES (current_date, 1)
ON CONFLICT (day) DO UPDATE SET present = daily_attendance_count.present + 1;

SELECT present FROM daily_attendance_count WHERE day = current_date;   -- 3

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding