Confirmation of ON CONFLICT DO NOTHING of upsert in PostgreSQL 9.5
I was expecting the upsert added in PostgreSQL 9.5 with “on conflict do nothing” to not go to insert if there is already data, but the same data was being inserted more and more, so I checked the behavior.
ISNERT INTO <table_name> VALUES (1,1,1) ON CONFLICT DO NOTHING;
When I write an insert statement like this, if there is already data in the table, it is not inserted and no error occurs, so it works as if nothing happened.
However, this is the case for tables with constraints (primary key), and for tables without constraints, it seems that the same data is inserted more and more.
ON CONFLICT ON CONSTRAINT ‘constraint name’
You can issue a merge statement with
ON CONFLICT ON CONSTRAINT constraint name.
You can specify constraint names with
ON CONFLICT ON CONSTRAINT constraint name, but not index names.
Cases where the ID (main key) is a surrogate key
INSERT INTO <table_name> (～) VALUES (～) ON CONFLICT ID SET ～
If the ID already exists, it can be updated.
If the ID uses a sequence object with a surrogate key, the ID can be omitted in the VALUES clause.
If the ID is omitted, NEXTVAL is executed and no update is performed. (Always insert)
The official site is not easy to understand.