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