JIYIK CN >

This article will introduce PostgreSQL INSERT ON DUPLICATE UPDATE. 插入重复更新What does it mean? Why should I use it?

Whenever you add 插入a record to the table, you add 主键a data set with a unique ID. It can be a different ID for each data set or an automatically generated number.

Suppose you insert a record with a primary key that conflicts with an already existing dataset with that primary key.

In this case, you have two options: Update the row with your new data set or keep the update and retain the original data.

What you tend to choose depends on how you want to sort your data and save it in your database.

ON CONFLICTClause in PostgreSQL

Upsertis UPDATE ON INSERT VIOLATIONan abbreviation for and can be implemented in the following query in PostgreSQL. First, assume we CARcreate a table for and insert a row.

create table car(
	id int PRIMARY KEY,
	owner TEXT
);

insert into car values (1, 'John');

Now let's go ahead and try to insert a value into this table (1, 'Mark'). What do you think will happen?

The first thing that will happen is VIOLATIONan error like the one shown below.

Output:

ERROR: duplicate key value violates unique constraint "car_pkey" DETAIL: Key (id)=(1) already exists.

Now, do you understand why a primary key violation occurs? Because Markalso has Johna key that already exists in 1.

To avoid this, we will use ON CONFLICTthe clause.

insert into car values (1, 'Mark')
on conflict (id) do update
set id = excluded.id,
	owner = excluded.owner;

Now, whenever our query finds a conflict, it will 更新remove the conflicting row from the table and set the sum 主键of the pre-existing data to these new keys.ID所有者

But EXCLUDEDwhat exactly is that table? EXCLUDEDA table represents the rows that are suggested for insertion, as specified by the PostgreSQL documentation.

Therefore, we use the table to get the rows pushed EXCLUDEDfrom the query and then use them.INSERT

Assuming you don't want to do anything with the violation and continue to keep the original. You're better off using this syntax.

insert into car values (1, 'Mark')
on conflict (id) do nothing;

DO NOTHINGIgnore any possible changes.

To psqlexecute these same queries in , use the above statements, but in uppercase KEYWORDS. Never write INSERTas insert, or you will get errors.

触发器Using Custom or 函数Implementing Upsert in PostgreSQL

Here we'll explain how to make one FUNCTIONthat fires when you call INSERTor UPDATEto implement . Again, you can create one UPSERTby changing the return type to .TRIGGERTRIGGER

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

The function is very simple, running a loop to check each row ID; if it matches the given parameters, it returns UPDATEthe table after; otherwise, it is INSERTS.

You can call the above like this.

select * from upsert_imp(1, 'Mark');

When doing TRIGGER, make sure to LOOPreplace with FORor a valid IFcheck so that it doesn't spin indefinitely and violate the condition.

Implementing Upsert in PostgreSQL using sequential statements (not recommended)

You can use UPDATEthe call, but INSERTcombine it with the following statement.

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

You could even use NOT EXISTSa SELECT clause, but this simple statement works fine. It 插入will either match .

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

However, there is a condition. In many systems, there may be RACEa condition.

If someone takes a line INSERTwhile you are taking a line DELETES, your line will be lost. To do this, you can TRANSACTIONuse

Enclose the statement with BEGINand to ensure that it is now .COMMITINSERTTRANSACTION

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

However, PostgreSQL already adds implicit BEGINand to every statement COMMIT, so explicit declaration is not necessary.

COMMON TABLE EXPRESSIONS (CTE)Implemented in PostgreSQLUPSERT

First of all, what is COMMON TABLE EXPRESSIONS?

CTEUsed as QUERYa temporary table in to store values ​​that may be used later. It behaves similarly to the table TRIGGERused in NEW.

The query can be written as follows. We first define WITH, which creates a temporary table containing the values 2, Jackson​​and 5, Maria.

These values ​​are then passed to UPSERTthe query, which NEW_VALUEScreates them from the table NV, or if they already exist CARin the table, it updates the values ​​accordingly.

WITH new_values (id, owner) as (
  values
     (2, 'Jackson'),
	 (5, 'Maria')

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

This returns a CAR.*, representing CARall the rows in the table.

Calling the above:

INSERT INTO car (id, owner)
SELECT id, owner
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 return already contains the value; if not, CAR.*valid . However, if it does, the modification has already been handled internally.UPSERTINSERT INTOUPSERT

ON CONFLICT DO UPDATENotes on PostgreSQL

As with every solution, some issues usually interfere with performance or space or coding efficiency.

In our query, whenever we call ON CONFLICT DO UPDATE, we can assume that after this, in our system, there can be multiple users accessing the database and issuing commands at the same time.

Therefore, if is issued at the same time as USER 1, the error will occur again . Why?USER 2INSERT DO UPDATEVIOLATION

Because when USER 2is called INSERT, it has no idea whether the row exists at that time; therefore, it conflicts. Two users issue queries at the same time, and the database runs them at the same time.

These kinds of questions are called RACE CONDITION, and you can read about them later if you like.

With RACE CONDITIONthe advent of , there are different ways to solve this problem. People argue that 交易may be a possible answer.

But even though TRANSACTIONguarantees that the queries are separated, it does not ensure that the queries are VIOLATIONsafe from . Why?

If USER 1``TRANSACTSit's the query, you can't guarantee that once TRANSACTIONis run, it will lock the query and not let other queries proceed. Because TRANSACTIONSwill only prevent the changes from being shown to other users, and the only option is to give up and roll back.

QUERIESEven if you use the ones that claim to run in order SERIAL TRANSACTIONS, you may still get errors like the following.

commit failed: ERROR:  could not serialize access due to read/write dependencies among transactions

Therefore, you are better off using . ADVISORY LOCKSand SAVE POINTS. ADVISORY LOCKStend to lock to prevent your queries from interfering and working efficiently.

Even though they might be able to reproduce the bug, the tendency to do so is still low.

On the other hand, SAVE POINTSincluded in TRANSACTIONS. Whenever you want TRANSACTIONto discard changes during and rollback, you can use these SAVEPOINTSto restore your changes.

So if INSERTfails, for example, and you just didn't skip the error, you can now roll back to SAVE POINT, execute UPDATE, and then work as usual.

We hope that you now have a clear UPSERTunderstanding of the basics of and a deeper understanding of how it works and what is possible CONSTRAINTS.

Our team works effectively to provide you the best of both worlds in terms of knowledge and efficient coding algorithms. We believe that the more you understand the nooks and crannies of things, the more effective you will be in providing solutions and resolving those problems.