How to Insert or Update if Exists in PostgreSQL

To insert a row when its key is new and update it when the key already exists, use PostgreSQL’s INSERT ... ON CONFLICT (UPSERT). The conflict target must match a unique constraint or unique index, such as a primary key. For example, with a users table whose id is the primary key, INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; inserts user 12 or replaces those three fields on the existing row.

The examples below assume this schema: CREATE TABLE users (id integer PRIMARY KEY, name text NOT NULL, gender text NOT NULL, age integer NOT NULL);. SQL is shown inline because the examples require a PostgreSQL server; the Python example is only a small model of the insert-or-update decision.

Run this example:

users = {}

def upsert(user):
    key = user["id"]
    if key in users:
        users[key].update(user)
    else:
        users[key] = user.copy()

upsert({"id": 12, "name": "John Kane", "age": 23})
upsert({"id": 12, "name": "John Kane", "age": 24})
print(users[12])
print("rows:", len(users))

{'id': 12, 'name': 'John Kane', 'age': 24}
rows: 1

This Python snippet illustrates the decision only; it does not connect to or execute SQL in PostgreSQL. The SQL patterns below are database examples and should be run against a table with the stated schema.

Use UPDATE Then INSERT to Insert or Update a Row if It Exists

An update followed by a conditional insert is a two-statement pattern. First run UPDATE users SET name = 'John Kane', gender = 'Male', age = 21 WHERE id = 12 RETURNING id;. If it returns a row, the existing user was updated. If it returns no rows, run INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 21);.

The application or transaction issuing these statements must decide whether to run the insert based on the UPDATE ... RETURNING result. A separate update and insert are not an atomic UPSERT: another transaction can insert the same key between them, causing a unique-key conflict. For a normal insert-or-update operation that must handle concurrent writers, prefer INSERT ... ON CONFLICT below.

Use INSERT ... ON CONFLICT (UPSERT) to Insert or Update a Row if It Exists

INSERT ... ON CONFLICT attempts the insert and handles a uniqueness conflict as part of the same statement. The conflict target must identify a usable unique constraint or index. In this example, id is the primary key, and EXCLUDED refers to the row proposed for insertion.

Use INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; to insert the row or replace its name, gender, and age when id = 12 already exists. If only selected fields should change, list only those fields after DO UPDATE SET; for example, DO UPDATE SET age = EXCLUDED.age preserves the existing name and gender.

The action determines the result: a new key inserts the supplied row, while an existing key updates the fields named in DO UPDATE SET. The statement can also use DO NOTHING when a conflict should leave the existing row unchanged. For community-maintained background, see the PostgreSQL Wiki UPSERT page.

Use Common Table Expression (CTE) to Insert or Update a Row if It Exists

A data-modifying CTE can connect an update’s RETURNING rows to a conditional insert. For example: WITH updated AS (UPDATE users SET name = 'John Kane', gender = 'Male', age = 25 WHERE id = 12 RETURNING id) INSERT INTO users (id, name, gender, age) SELECT 12, 'John Kane', 'Male', 25 WHERE NOT EXISTS (SELECT 1 FROM updated);.

The insert runs only when the update returned no row in that statement. This can be useful when the update and fallback insert belong in one SQL statement, but it is not a substitute for ON CONFLICT under concurrent writes. A concurrent insert may still cause a unique-key violation, so use INSERT ... ON CONFLICT when you need the usual conflict-safe UPSERT behavior.

Use PL/pgSQL Function to Insert or Update a Row if It Exists

A PL/pgSQL function can package the recommended conflict-handling statement for reuse. Give parameters names that cannot be confused with column names. For example, the function body can execute INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; with parameters p_id integer, p_name text, p_gender text, and p_age integer.

Define the function with CREATE OR REPLACE FUNCTION upsert_user(p_id integer, p_name text, p_gender text, p_age integer) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; END; $$;, then call it with SELECT upsert_user(13, 'New User', 'Female', 30);. This delegates the same unique-key conflict handling to the database; the function does not make a separate update-then-insert race necessary.

Conclusion

For the usual conditional write, use INSERT ... ON CONFLICT (UPSERT) with a primary key or other suitable unique constraint as the conflict target. It inserts a new row and applies the fields you choose when that key already exists. An update followed by an insert and an update-then-insert CTE can illustrate the two outcomes, but they do not provide the same concurrent conflict handling. Use a PL/pgSQL function when you want to reuse the conflict-handling operation inside the database.