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

1. What Is EXCLUDED in PostgreSQL
2. the Basic EXCLUDE Usage in PostgreSQL
3. Structural Differences to the Basic EXCLUDED Usage in PostgreSQL

Last time, we read about how we could use UPSERT in PostgreSQL.

In a quick review, UPSERT is short for INSERT ON DUPLICATE UPDATE that tends to INSERT values into the table if they don’t match previous entries. If they do, they are automatically updated.

What Is EXCLUDED in PostgreSQL

EXCLUDED is the name the DBMS gives to a special table where we have all the rows proposed for INSERTION present. These rows may be inserted to this table as soon as the INSERT operation runs.

This is mostly preceded by the ON CONFLICT DO UPDATE clause, specifically targeting this table. Also, the SET and WHERE clauses tend to have the privilege to access this EXCLUDED table.

So the next time you try to INSERT something, you can use the EXCLUDED table if it seems to fulfill your needs. We hope you are clear on the basic terminology used in the article and the mechanism behind it.

Let’s move towards learning some ways, many of which are short and efficient work-arounds, to implement the usage of the EXCLUDED table.

the Basic EXCLUDE Usage in PostgreSQL

The first, very simple use of EXCLUDE can be implemented.

1. Let’s create a TABLE for animals.

   create table animal(
   	id int PRIMARY KEY,
   	age int,
   	type TEXT
   );
   

   TYPE here represents the animal type. It could be a cat, dog, horse, etc.

2. Let’s insert a few values.

   insert into animal values (1, 10, 'Dog'), (2, 12, 'Horse')
   

3. Now, let’s go ahead and write the query for EXCLUDED.

   insert into animal values (1, 3, 'Cat'), (3, 4, 'Kitten')
   on conflict (id) do update set id = excluded.id, age = excluded.age, type = excluded.type;
   

So what’s happening here? First of all, there is a duplicate.

The set (1, 3, 'Cat') violates the unique constraint of PRIMARY KEY as there is already (1, 10, 'Dog') present with the key 1.

So we call the ON CONFLICT DO UPDATE, and then as soon as it catches the violation, we set the keys of that row to the keys of the new data set that is being inserted, hence overwriting the previous entry.

The output would be something like this.

Output:

However, it depends if you want to do this. In case, on insertion, you might not want to overwrite, rather keep the previous entry intact, then you should not be, in that case, using this query at all.

Structural Differences to the Basic EXCLUDED Usage in PostgreSQL

In case users may try to keep the statements compact and readable, they may try to avoid using the above query where:

set id = excluded.id, age = excluded.age, type = excluded.type;

This may get messy and later cause problems if a wide range of data is INSERTED or UPDATED.

A much easier way would be to use this instead.

set (id, age, type) = (EXCLUDED.id, EXCLUDED.age, EXCLUDED.type)

This tends to group the elements and is pretty much the same as described above but more orderly and readable.