The introduction
This feature is supported by PostgreSQL9.5. If you want to insert a piece of data that already has a primary key, you cannot insert it. With the upset sentence, you can insert nothing and update something.
grammar
INSERT INTO VALUES ()'numerical 1'.'numerical 2',...). ON CONFLICT ON CONSTRAINT DO UPDATE SET column 1='interesting', 2 = column'interesting'. ;Copy the code
1. Try to Upset
First, create a table and insert a piece of data.
create table m_user (
username character varying(8) not null
, password character varying(16)
, auth character varying(16)
, primary key (username)
);
Copy the code
insert into m_user values('0001'.'0001'.'admin');Copy the code
Then investigate the constraint name for the table.
select table_name, constraint_name, constraint_type
from information_schema.table_constraints
where table_name='m_user';
Copy the code
You can see that the constraint is called m_user_pkey.
Of course, you can also customize the restriction names at table creation time.
create table m_user (
username character varying(8) not null
, password character varying(16)
, auth character varying(16)
, constraint m_user_pkey primary key (username)
);
Copy the code
Try the following statement with the constraint name specified m_user_pkey. If username 0001 already exists, change its password to 0002.
Execute the statement
insert into m_user values('0001'.'0002'.'admin')
on conflict on constraint m_user_pkey
do update set password='0002';
Copy the code
The execution result
The username | password | auth -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- 0001 | 0002 | admin (line 1)Copy the code
2. Try using DO NOTHING
If you continue with the previous table, you will find that no data is updated as a result, but the primary key duplicate error is avoided. If you have this scenario in development and you don’t want to go wrong, you can use Do nothing.
Execute the statement
insert into m_user values('0001'.'0002'.'admin')
on conflict on constraint m_user_pkey
do nothing;
Copy the code