In PostgreSQL, UPSERT
UPSERT means update if the row exists, otherwise insert it.
It combines INSERT + UPDATE into one operation.
In PostgreSQL this is done using:
INSERT ... ON CONFLICT
Basic Idea
Suppose you have a table:
CREATE TABLE users (
id INT PRIMARY KEY,
name TEXT
);If you try:
INSERT INTO users (id, name)
VALUES (1, 'Delwar');
If id = 1 already exists → PostgreSQL will throw an error.
UPSERT solves this.
UPSERT Example
INSERT INTO users (id, name)
VALUES (1, 'Delwar')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;
Behavior:
CaseResultid does not existINSERTid existsUPDATE
What is EXCLUDED?
EXCLUDED represents the new value you tried to insert.
Example:
INSERT INTO users (id, name)
VALUES (1, 'Rahim')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;
If row exists:
id | name
1 | Delwar
After UPSERT:
id | name
1 | Rahim
Do Nothing Option
Sometimes you want to skip duplicates.
INSERT INTO users (id, name)
VALUES (1, 'Delwar')
ON CONFLICT (id)
DO NOTHING;
If id = 1 exists → nothing happens.
Real Example (Similar to Your Migration Case)
Suppose you have:
OrganizationReportPermission
organizationId UNIQUE
You can UPSERT like this:
INSERT INTO "OrganizationReportPermission"
("organizationId", "permissionToggles", "version")
VALUES ('org-123', '{}', 1)
ON CONFLICT ("organizationId")
DO UPDATE
SET "permissionToggles" = EXCLUDED."permissionToggles",
"updatedAt" = now();Meaning:
CaseActionorganization not existscreate roworganization existsupdate permission
Why UPSERT is Important
Without UPSERT you must do:
SELECT
IF EXISTS
UPDATE
ELSE
INSERT
That causes race conditions.
Example problem:
Process A -> SELECT (not found)
Process B -> SELECT (not found)
Process A -> INSERT
Process B -> INSERT -> ERROR
UPSERT is atomic and prevents this.
Short Summary
UPSERT =
INSERT
if conflict
UPDATE
PostgreSQL syntax:
INSERT ...
ON CONFLICT (...)
DO UPDATE ...
✅ Simple rule
CommandMeaningINSERTalways insertUPDATEupdate existingUPSERTinsert or update
Comments (0)
Login to leave a comment.