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

1. 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.

1. 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

1. 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

1. 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.

1. 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

1. 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.

1. Short Summary

UPSERT =

INSERT

if conflict

UPDATE

PostgreSQL syntax:

INSERT ...

ON CONFLICT (...)

DO UPDATE ...

✅ Simple rule

CommandMeaningINSERTalways insertUPDATEupdate existingUPSERTinsert or update