How to Upsert (Merge, Insert on Duplicate Update) in PostgreSQL

1. What Is UPSERT in PostgreSQL
2. Use the ON CONFLICT (PK_Name) DO UPDATE Operation to Upsert in PostgreSQL
3. Use Either a Custom Made Trigger or Function to Implement Upsert in PostgreSQL
4. Use Sequential Statements (Less - Recommended) to Implement Upsert in PostgreSQL
5. Use Common Table Expressions (CTE) to Implement UPSERT in PostgreSQL

This article describes how to perform an upsert operation in PostgreSQL.

What Is UPSERT in PostgreSQL

UPSERT is a term coined by combining UPDATE and INSERT operations in database queries. It is not a keyword in either MySQL or PostgreSQL, the two DBMS we most commonly discussed.

Thus, we can not use UPSERT, either a clause or an operator. However, it may exist as a proper clause in other database systems.

UPSERT is simple; it would update the older data set if you want to insert data into a table, and somehow, this data is already present before (Duplication). If it isn’t present before, it will insert the data set without modification.

Let’s say we have the following data.

And now we want to insert the following statement.

Insert into car values(2, 'Toyota Vigo')

The INSERT would return a duplicate error as the key already exists.

However, if we do this using an UPSERT method, the data will be updated, and the results would be:

Remember that there is no specific UPSERT method, only a term given to a set of operations that would be able to do something like this.

In MySQL, you can achieve UPSERT using the following statement.

INSERT INTO car values (2, 'Toyota Corolla') ON DUPLICATE KEY UPDATE name = 'Toyota Vigo';

This will detect the DUPLICATE KEY -> 2 in the table and then call the UPDATE method to update that row.

Now let’s go ahead and learn how to implement this in PostgreSQL.

Use the ON CONFLICT (PK_Name) DO UPDATE Operation to Upsert in PostgreSQL

Let’s use the same CAR table we made above. Let’s suppose we want to insert some data set into the table in PostgreSQL.

insert into car values(2, 'Toyota Vigo')
ON CONFLICT (id) DO UPDATE
							SET id = excluded.id,
								name = excluded.name
;

Do you see the ON CONFLICT followed by the PRIMARY KEY and the DO UPDATE operation?

Well, it checks the PRIMARY KEY, which is ID in our case, and if it finds a CONFLICT, it will UPDATE rather than throw an error. You will see the term EXCLUDED in the query.

EXCLUDED is a table with rows proposed for insertion in PostgreSQL. So when you happened to call INSERT for the new data set, your data eventually appended itself to the table EXCLUDED.

Use Either a Custom Made Trigger or Function to Implement Upsert in PostgreSQL

We will explain how to make a FUNCTION that will fire off whenever INSERT or UPDATE is called for implementing UPSERT. Similarly, you can make a TRIGGER by changing the return type to TRIGGER.

create or replace function upsert_imp (idt int, namet TEXT) returns void as
$$
	Begin
		loop
			update car set name = namet where id = idt;
		if found then
			return;
		end if;
		begin
			insert into car values(namet, idt);
			return;
		end;
		end loop;
	end;
$$ language plpgsql;

The function is pretty simple, with a loop that runs for checking the ID for each row; if it matches the parameter given, then it returns the table after UPDATE, else it INSERTS.

You can call the above like below.

select * from upsert_imp(2, 'Toyota Supra');

This will now return an output like below.

While making a TRIGGER, make sure to replace LOOP with a FOR or a valid IF check so that it does not spin indefinitely in violation of a condition.

Use Sequential Statements (Less - Recommended) to Implement Upsert in PostgreSQL

You can use the UPDATE call but combine it with the following INSERT statement to work.

insert into car values(4, 'Toyota Supra') on conflict do nothing;

You may even use a NOT EXISTS clause, but this easy statement works fine. It will either INSERT the row if there is no duplication or skip entirely.

insert into car select 2, 'Toyota Supra' where not exists (Select 1 from CAR where id=2);

However, there’s a condition. In many systems, there could be a RACE condition.

If someone DELETES a row at which the same time you INSERT a row, your row will be lost. For that, you can use a TRANSACTION.

Enclose the INSERT statement with a BEGIN and COMMIT to ensure it is now a TRANSACTION.

begin;
insert into car select 2, 'Toyota Supra' where not exists (Select 1 from CAR where id=2);
commit;

However, PostgreSQL already has implicit BEGIN and COMMIT added to each statement, so there’s no need for explicit declarations.

Use Common Table Expressions (CTE) to Implement UPSERT in PostgreSQL

First of all, what are Common Table Expressions (CTE)?

CTE works as a temporary table inside a query to store values that may be used later on. It behaves like the NEW table used in TRIGGER.

The query can be written as follows. We first define WITH, which creates a temporary table with the values 2, Toyota Supra and 5, Honda City.

Then these values are passed into the UPSERT query, where it creates NV from the NEW_VALUES table, and if they turn out to be already present in the CAR table, it updates the values accordingly.

WITH new_values (id, name) as (
  values
     (2, 'Toyota Supra'),
	 (5, 'Honda City')

),
upsert as
(
    update car
        set id = nv.id,
            name = nv.name
    FROM new_values nv
    WHERE car.id = nv.id
    RETURNING car.*
)

This returns a CAR.*, meaning all the rows from the CAR table back.

To call the above:

INSERT INTO car (id, name)
SELECT id, name
FROM new_values
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = new_values.id);

Here, we check if the table created in the UPSERT (when it returns the CAR.*) contains the value already; if it doesn’t, the INSERT INTO works. However, if it does, the UPSERT already handles the modification inside.

These are the possible ways to create an UPSERT implementation in PostgreSQL. We hope this helps you!