When working with databases, you often need to insert a new record if it doesn’t exist, or update an existing record if it already exists.

This operation is commonly called an UPSERT:

UPDATE + INSERT = UPSERT

PostgreSQL provides a powerful and convenient way to perform UPSERT operations using:

INSERT ... ON CONFLICT

Instead of first checking whether a row exists and then deciding whether to insert or update it, PostgreSQL can handle the operation in a single SQL statement.

In this guide, we’ll explore PostgreSQL UPSERT with INSERT ON CONFLICT, including basic syntax, unique constraints, DO NOTHING, DO UPDATE, EXCLUDED, conditional updates, multiple columns, and practical application examples.

---

What Is UPSERT?

Suppose you have a users table:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL
);

You want to insert:

oliver@example.com

If that email already exists, you want to update the user’s name instead.

Without UPSERT, you might need:

Check whether user exists
        ↓
     Exists?
     /     \
   Yes      No
    ↓        ↓
 UPDATE    INSERT

With PostgreSQL:

INSERT
  ↓
Conflict?
 /       \
Yes       No
 ↓         ↓
UPDATE    INSERT

This is what INSERT ... ON CONFLICT makes possible.

---

Basic INSERT ON CONFLICT Syntax

The basic structure is:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (conflict_column)
DO UPDATE
SET column2 = EXCLUDED.column2;

For example:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

If the email doesn’t exist:

→ INSERT

If the email already exists:

→ UPDATE

---

Why Is It Called UPSERT?

The name comes from:

UPDATE
+
INSERT
=
UPSERT

PostgreSQL doesn’t have a command literally named:

UPSERT

Instead, PostgreSQL implements UPSERT behavior using:

INSERT ... ON CONFLICT

---

Creating a Unique Constraint

ON CONFLICT needs a conflict target or a suitable unique/exclusion constraint to determine what constitutes a conflict.

For example:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL
);

Here:

email
  ↓
UNIQUE
  ↓
Cannot contain duplicate values

That gives PostgreSQL a uniqueness rule it can use for conflict detection.

---

Basic DO NOTHING

Sometimes you don’t want to update an existing record.

You simply want to ignore the duplicate.

Use:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO NOTHING;

If the email already exists:

→ Nothing happens

No error is returned for that conflict.

---

DO NOTHING Example

Suppose the table contains:

id | email
---|---------------------
1  | oliver@example.com

Run:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO NOTHING;

PostgreSQL detects the conflict and skips the insert.

The existing row remains unchanged.

---

DO UPDATE

If you want to update the existing row:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

The important part is:

EXCLUDED.name

---

What Is EXCLUDED?

EXCLUDED represents the row that PostgreSQL attempted to insert but couldn’t insert because of a conflict.

For example:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

Think of it as:

Existing row
     +
New incoming row
     ↓
Conflict
     ↓
EXCLUDED = incoming row

So:

EXCLUDED.name

means:

The name value from the attempted INSERT.

---

Existing Value vs EXCLUDED Value

Suppose the database contains:

email: oliver@example.com
name:  John

You execute:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

Then:

Existing name → John
Incoming name → Oliver

After the UPSERT:

name → Oliver

because:

EXCLUDED.name

contains:

Oliver

---

Updating Multiple Columns

You can update multiple columns:

INSERT INTO users (email, name, is_active)
VALUES ('oliver@example.com', 'Oliver', TRUE)
ON CONFLICT (email)
DO UPDATE
SET
    name = EXCLUDED.name,
    is_active = EXCLUDED.is_active;

If the user already exists, both values are updated.

---

UPSERT With a Counter

UPSERT becomes especially useful for counters.

Suppose:

CREATE TABLE product_views (
    product_id BIGINT PRIMARY KEY,
    views BIGINT NOT NULL DEFAULT 0
);

You can write:

INSERT INTO product_views (product_id, views)
VALUES (10, 1)
ON CONFLICT (product_id)
DO UPDATE
SET views = product_views.views + EXCLUDED.views;

If product 10 doesn’t exist:

product_id = 10
views = 1

If it already exists with:

views = 100

the result becomes:

views = 101

---

Why EXCLUDED Is Useful for Counters

Here:

product_views.views

means the existing value.

While:

EXCLUDED.views

means the incoming value.

Therefore:

product_views.views + EXCLUDED.views

means:

Existing views + Incoming views

This makes UPSERT useful for aggregation and counters.

---

UPSERT With Created and Updated Timestamps

A common pattern is:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Then:

INSERT INTO users (
    email,
    name
)
VALUES (
    'oliver@example.com',
    'Oliver'
)
ON CONFLICT (email)
DO UPDATE
SET
    name = EXCLUDED.name,
    updated_at = NOW();

On insert:

created_at → automatically set
updated_at → automatically set

On conflict:

created_at → remains unchanged
updated_at → updated

---

UPSERT With a WHERE Condition

You can conditionally update a conflicting row.

For example:

INSERT INTO products (
    id,
    price,
    updated_at
)
VALUES (
    10,
    99.99,
    NOW()
)
ON CONFLICT (id)
DO UPDATE
SET
    price = EXCLUDED.price,
    updated_at = EXCLUDED.updated_at
WHERE products.updated_at < EXCLUDED.updated_at;

This means the existing record is updated only when the incoming record is newer.

This pattern can be useful when synchronizing data.

---

Conditional UPSERT

Suppose you have:

Existing score → 80
Incoming score → 75

You only want to update when the incoming score is higher.

INSERT INTO scores (user_id, score)
VALUES (10, 75)
ON CONFLICT (user_id)
DO UPDATE
SET score = EXCLUDED.score
WHERE EXCLUDED.score > scores.score;

If the existing score is 80, nothing changes.

If the incoming score is 90, the row is updated.

---

UPSERT With Multiple Conflict Columns

A unique constraint can cover multiple columns.

For example:

CREATE TABLE memberships (
    user_id BIGINT NOT NULL,
    team_id BIGINT NOT NULL,
    role TEXT NOT NULL,
    UNIQUE (user_id, team_id)
);

Now:

INSERT INTO memberships (
    user_id,
    team_id,
    role
)
VALUES (
    10,
    5,
    'admin'
)
ON CONFLICT (user_id, team_id)
DO UPDATE
SET role = EXCLUDED.role;

The combination:

user_id + team_id

determines whether a conflict occurs.

---

UPSERT With DO NOTHING and Multiple Columns

You can also write:

INSERT INTO memberships (
    user_id,
    team_id,
    role
)
VALUES (
    10,
    5,
    'admin'
)
ON CONFLICT (user_id, team_id)
DO NOTHING;

This simply ignores the duplicate combination.

---

UPSERT Using a Named Constraint

You can also reference a specific unique or exclusion constraint:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT ON CONSTRAINT users_email_key
DO UPDATE
SET name = EXCLUDED.name;

This can be useful when the constraint itself is the clearest way to identify the conflict rule.

---

UPSERT With Partial Unique Indexes

PostgreSQL also supports conflict handling involving appropriate unique indexes, including partial unique indexes when the conflict target specifies the matching predicate.

For example:

CREATE UNIQUE INDEX unique_active_email
ON users(email)
WHERE is_active = TRUE;

A corresponding UPSERT can specify the conflict condition:

INSERT INTO users (email, name, is_active)
VALUES ('oliver@example.com', 'Oliver', TRUE)
ON CONFLICT (email)
WHERE is_active = TRUE
DO UPDATE
SET name = EXCLUDED.name;

This is an advanced pattern and should be designed carefully around the exact uniqueness rule you need.

---

UPSERT With UUID

UPSERT works with UUID primary keys too.

For example:

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

If another unique field identifies the product:

CREATE UNIQUE INDEX idx_products_sku
ON products(sku);

You can write:

INSERT INTO products (sku, name, price)
VALUES ('LAP-001', 'Laptop', 999.99)
ON CONFLICT (sku)
DO UPDATE
SET
    name = EXCLUDED.name,
    price = EXCLUDED.price;

The UUID primary key remains unchanged when the SKU causes the conflict.

---

UPSERT With Node.js

PostgreSQL UPSERT is commonly used in Node.js applications.

Using a PostgreSQL client:

const query = `
    INSERT INTO users (email, name)
    VALUES ($1, $2)
    ON CONFLICT (email)
    DO UPDATE
    SET name = EXCLUDED.name
    RETURNING *;
`;

const values = [
    'oliver@example.com',
    'Oliver'
];

const result = await pool.query(query, values);

The important points are:

$1, $2
→ Parameterized values

ON CONFLICT
→ Handles duplicates

RETURNING *
→ Returns the inserted/updated row

---

UPSERT With RETURNING

PostgreSQL supports RETURNING with INSERT.

For example:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name
RETURNING *;

This allows the application to receive the resulting row without issuing a separate SELECT.

You can also return specific columns:

RETURNING id, email, name;

---

Why RETURNING Is Useful

Without RETURNING, an application might do:

UPSERT
 ↓
SELECT
 ↓
Return data

With:

RETURNING *

you can often do:

UPSERT
 ↓
Return resulting row

This can reduce unnecessary database round trips.

---

UPSERT vs SELECT Then INSERT/UPDATE

A common approach is:

SELECT
  ↓
Does row exist?
  ↓
YES → UPDATE
NO  → INSERT

This has a major problem under concurrency.

Imagine:

Transaction A → SELECT → row doesn't exist
Transaction B → SELECT → row doesn't exist
Transaction A → INSERT
Transaction B → INSERT

Now both transactions may attempt to create the same record.

A unique constraint plus ON CONFLICT lets PostgreSQL handle this race at the database level.

---

Why ON CONFLICT Is Safer Under Concurrency

Consider:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

PostgreSQL handles the uniqueness conflict atomically according to its concurrency and constraint rules.

This is generally preferable to manually implementing:

SELECT → check → INSERT

when the operation is fundamentally an insert-or-update action.

---

UPSERT and Transactions

UPSERT can be used inside transactions.

For example:

BEGIN;

INSERT INTO users (...)
VALUES (...)
ON CONFLICT (...)
DO UPDATE
SET ...;

INSERT INTO user_profiles (...)
VALUES (...);

COMMIT;

If the transaction fails:

ROLLBACK;

The UPSERT is rolled back along with the other transaction changes.

---

UPSERT and Unique Constraints

The conflict mechanism relies on a uniqueness or exclusion rule.

For example:

email TEXT UNIQUE

or:

UNIQUE (user_id, team_id)

Without an appropriate conflict target, PostgreSQL cannot know which conflict you want ON CONFLICT to handle.

---

What Happens Without a Unique Constraint?

Suppose:

CREATE TABLE users (
    email TEXT,
    name TEXT
);

There is no unique rule on email.

Then:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

will fail because there is no matching unique or exclusion constraint/index for the conflict target.

You should define the appropriate uniqueness rule first.

---

UPSERT With Composite Unique Constraints

Suppose:

CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    assigned_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (user_id, role_id)
);

UPSERT:

INSERT INTO user_roles (
    user_id,
    role_id
)
VALUES (
    10,
    3
)
ON CONFLICT (user_id, role_id)
DO UPDATE
SET assigned_at = NOW();

This is useful when the combination of two or more fields uniquely identifies a record.

---

UPSERT for Synchronization

UPSERT is useful when synchronizing data between systems.

For example:

External API
     ↓
Application
     ↓
PostgreSQL

The application can receive records and use:

INSERT ... ON CONFLICT

to either:

Create new record

or:

Update existing record

This is common in:

• Data synchronization
• Import jobs
• ETL pipelines
• Integration systems
• Background workers

---

UPSERT for Inventory

Consider an inventory table:

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    quantity INTEGER NOT NULL
);

You can increment inventory:

INSERT INTO inventory (product_id, quantity)
VALUES (10, 5)
ON CONFLICT (product_id)
DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity;

If product 10 doesn’t exist:

quantity = 5

If it already has:

quantity = 20

the result becomes:

quantity = 25

---

UPSERT for User Preferences

Suppose:

CREATE TABLE user_preferences (
    user_id BIGINT PRIMARY KEY,
    theme TEXT NOT NULL,
    language TEXT NOT NULL
);

You can write:

INSERT INTO user_preferences (
    user_id,
    theme,
    language
)
VALUES (
    10,
    'dark',
    'en'
)
ON CONFLICT (user_id)
DO UPDATE
SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language;

This creates the preferences if they don’t exist and updates them if they do.

---

UPSERT for API Data

Suppose an external API provides:

{
  "external_id": "customer_123",
  "name": "Oliver",
  "email": "oliver@example.com"
}

You could define:

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_id TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

Then:

INSERT INTO customers (
    external_id,
    name,
    email
)
VALUES (
    'customer_123',
    'Oliver',
    'oliver@example.com'
)
ON CONFLICT (external_id)
DO UPDATE
SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

This is a common synchronization pattern.

---

UPSERT vs MERGE

Modern PostgreSQL also supports the SQL-standard MERGE command.

Both can be used for conditional data modification, but they serve somewhat different purposes.

INSERT ... ON CONFLICT

Excellent for:

Insert
+
Handle uniqueness conflict

Example:

INSERT ... ON CONFLICT

MERGE

Useful for more general conditional synchronization logic involving matched and unmatched rows.

For a straightforward “insert if missing, update if duplicate” operation, ON CONFLICT is often simpler.

---

UPSERT vs MERGE Example

Simple UPSERT:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

This is concise and directly expresses the intent.

For more complex matching logic, MERGE may be worth considering.

---

DO NOTHING vs DO UPDATE

Use:

ON CONFLICT DO NOTHING

when:

Existing record should remain unchanged

Use:

ON CONFLICT DO UPDATE

when:

Existing record should be modified

For example:

Duplicate registration
→ DO NOTHING

Updated customer data
→ DO UPDATE

Incrementing counters
→ DO UPDATE

Idempotent event processing
→ Often DO NOTHING or DO UPDATE

---

Idempotency and UPSERT

UPSERT is useful for building idempotent operations.

Suppose an external system sends the same event twice.

You can create a unique event identifier:

CREATE TABLE events (
    event_id UUID PRIMARY KEY,
    payload JSONB NOT NULL
);

Then:

INSERT INTO events (event_id, payload)
VALUES ($1, $2)
ON CONFLICT (event_id)
DO NOTHING;

If the same event arrives again, PostgreSQL ignores the duplicate.

This can help prevent duplicate processing at the database layer.

---

UPSERT and Race Conditions

Without a database uniqueness constraint, application-level checks can still suffer from race conditions.

Avoid relying only on:

if (!exists) {
    insert();
}

Two requests can execute the check at the same time.

Instead, enforce uniqueness in PostgreSQL and use:

INSERT ... ON CONFLICT

where appropriate.

---

UPSERT Best Practices

1. Define the Correct Unique Constraint

For example:

UNIQUE (email)

or:

UNIQUE (user_id, product_id)

2. Use Parameterized Queries

Don’t build SQL by concatenating user input.

3. Use EXCLUDED Correctly

Remember:

EXCLUDED
→ Incoming row

4. Return the Result When Needed

Use:

RETURNING

to avoid an unnecessary SELECT.

5. Keep Updates Focused

Don’t update every column if only one or two need changing.

6. Consider Concurrency

Let PostgreSQL’s unique constraints and conflict handling enforce the invariant.

7. Monitor High-Volume UPSERT Workloads

Heavy UPSERT activity can increase:

WAL generation
Index updates
Table bloat
CPU usage
Lock contention

---

Common UPSERT Mistakes

Mistake 1: No Unique Constraint

ON CONFLICT needs an appropriate conflict target.

---

Mistake 2: Confusing Existing and Incoming Values

Remember:

table.column

refers to the existing row in the DO UPDATE context, while:

EXCLUDED.column

refers to the proposed inserted value.

---

Mistake 3: Updating Unnecessary Columns

Avoid:

DO UPDATE
SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    created_at = EXCLUDED.created_at,
    ...

if only one field needs to change.

---

Mistake 4: Using SELECT Before Every INSERT

If the actual requirement is an insert-or-update operation, don’t always implement:

SELECT
→ application decision
→ INSERT/UPDATE

Use database-level conflict handling when appropriate.

---

Mistake 5: Forgetting Authorization

UPSERT solves data modification logic, not application authorization.

Always ensure the caller is allowed to modify the target record.

---

PostgreSQL UPSERT Cheat Sheet

Insert Only

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver');

Ignore Duplicate

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO NOTHING;

Update on Duplicate

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name;

Update Multiple Columns

ON CONFLICT (email)
DO UPDATE
SET
    name = EXCLUDED.name,
    is_active = EXCLUDED.is_active;

Return Result

ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name
RETURNING *;

Conditional Update

ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name
WHERE EXCLUDED.name <> users.name;

---

Final Thoughts

PostgreSQL’s INSERT ... ON CONFLICT provides a clean way to implement UPSERT operations without manually checking whether a row exists first.

The basic pattern is:

INSERT
  ↓
Conflict?
 ┌───────┴───────┐
 ↓               ↓
No              Yes
 ↓               ↓
INSERT      DO NOTHING
                or
             DO UPDATE

The most important concepts to remember are:

INSERT
→ Attempts to create the row

ON CONFLICT
→ Defines what happens when a uniqueness conflict occurs

DO NOTHING
→ Ignore the conflict

DO UPDATE
→ Modify the existing row

EXCLUDED
→ Represents the incoming row

For example:

INSERT INTO users (email, name)
VALUES ('oliver@example.com', 'Oliver')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name
RETURNING *;

This single statement can handle both creation and update while allowing PostgreSQL to enforce the underlying uniqueness rule.

For simple insert-or-update workflows, INSERT ... ON CONFLICT is one of the most useful PostgreSQL features to know.