JIYIK CN >

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

In a quick review, UPSERTis short for , which tends to insert values ​​into the table INSERT ON DUPLICATE UPDATEif they don't match a previous entry . If they do, they are automatically updated.INSERT

EXCLUDEDWhat is in PostgreSQL

EXCLUDEDis the name given by the DBMS to a special table in which we have INSERTIONall the rows proposed for . Once INSERTthe operation runs, these rows may be inserted into this table.

This is mainly ON CONFLICT DO UPDATEbefore the clause, specifically for this table. In addition, the SETand WHEREclauses often have EXCLUDEDpermissions to access this table.

So, next time you try INSERTsomething, if it seems to suit your needs, you can use EXCLUDEDa table. We hope that you are clear about the basic terminology used in this article and the mechanisms behind it.

Let's start by learning some methods, many of which are short but effective workarounds, to achieve EXCLUDEDthe use of tables.

EXCLUDEBasic usage in PostgreSQL

A very simple first EXCLUDEusage can be achieved.

1. Let's create one for the animals TABLE.

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

   TYPEThis represents the type of animal. It can be a cat, dog, horse, etc.

2. Let's plug in some values.

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

3. Now, let's move on to writing EXCLUDEDour query.

   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 going on here? First, there's a copy.

The collection (1, 3, 'Cat')violates PRIMARY KEYthe unique constraint of because a with key already 1exists (1, 10, 'Dog').

So we call ON CONFLICT DO UPDATE, and then once it finds a violation, we set the key of that row to the key of the new dataset being inserted, thus overwriting the previous entry.

The output will be something like this.

Output:

However, it depends on whether you want to do this or not. If while inserting, you probably don't want to overwrite but keep the previous entry intact, in which case you shouldn't use this query at all.

EXCLUDEDStructural differences in basic usage in PostgreSQL

If a user might be trying to keep statements compact and readable, they might try to avoid using the above query, where:

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

If a lot of data is INSERTEDor UPDATED, this can become confusing and subsequently cause problems.

A simpler approach is to use this instead.

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

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