UPSERT - INSERT ON CONFLICT - Postgres 9.5 İle Geliyor

Giriş

Açıklaması şöyle. UPSERT sanırım SQL:2003 ile geliyor. UPSERT işleminin ORACLE, SQL Server, PostgreSQL ve MySQL tarafından nasıl yapıldığına dair bir yazı burada

PostgreSQL doesn't have a dedicated UPSERT command, but upserts can be accomplished using INSERT ON CONFLICT

Açıklaması şöyle

Postgres 9.5 and above FINALLY supports UPSERT (otherwise known as: INSERT ... ON CONFLICT DO ).

Söz dizimi şöyle

INSERT INTO table (col1, col2, col3) 
VALUES (val1, val2, val3) 
ON CONFLICT conflict_target conflict_action;

Açıklaması şöyle

As we can see in the above command, PostgreSQL allows us to specify two things:

• conflict_target, i.e. where it should look to detect a conflict.
• conflict_action, i.e. how the command should be handled if a conflict is detected.

1. conflict_target

Sütun isimlerini belirtir

2. conflict_action

DO NOTHING veya DO UPDATE SET olabilir

2.1 DO NOTHING

Örnek

Şöyle yaparız

INSERT INTO "public"."post_details" (
    "id",
    "created_by",
    "created_on")
VALUES (
    1,
    'Alice',
    TIMESTAMP '2017-11-06 16:42:37.692')
ON CONFLICT DO NOTHING

2.2 DO UPDATE SET

Burada DO UPDATE SET ten sonra tüm sütunları belirtmek lazım. Açıklaması şöyle

It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict.

Örnek

Docker'ı çalıştır

docker run --name pg -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=sampledb -p 5432:5432 \
  postgres

Şöyle yap

CREATE TABLE customer (
  id NUMERIC, 
  name VARCHAR(20), 
  age NUMERIC, 
  address VARCHAR(20), 
  salary NUMERIC,
  PRIMARY KEY (id)
);

INSERT INTO customer (id,name,age,address,salary)
VALUES (1, 'orcun', 47, 'ankara', 2000);

INSERT INTO customer (id,name,age,address,salary) 
VALUES (1, 'colak', 46,  'istanbul', 3000)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; 

Örnek

Şöyle yaparız. Burada SQL cümlesi tam değil. 

INSERT INTO employees (id, name, email) 
VALUES (2, ‘Dennis’, ‘dennisp@weyland.corp’)
ON CONFLICT (id) DO UPDATE SET ...;

Açıklaması şöyle

PostgreSQL detects a conflict - we're trying to insert a row with an id value of 2, but a row with that id already exists in employees - so it runs UPDATE on that row using the new values.

If we were to run this command with values that did not generate a conflict (for example, (5, 'Kane', 'kane@weyland.corp'), it would insert a new row into employees with those values.

Örnek - Composite Key

Composite Key varsa ON CONFLICT() içinde virgül ile ayrılarak belirtilir. Şöyle yaparız. Burada PRIMARY KEY (person_id, question_id) 

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;
 person_id | question_id | answer 
-----------+-------------+--------
         1 |           1 | q1
(1 Zeile)

INSERT INTO answer VALUES (1,1,'q1-UPDATED') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;
 person_id | question_id |   answer   
-----------+-------------+------------
         1 |           1 | q1-UPDATED

Örnek 

Şöyle yaparız. 

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email;

EXCLUDED.email ile yeni veri kastediliyor. Satırdaki email değeri şöyle olur hotline@microsoft.com;contact@microsoft.com

Örnek

Şöyle yaparız. Burada birden fazla satır var, EXCLUDED.value ile belirttiğimiz veriye erişiriz. Ayrıca t.value ile mevcut değere de erişiriz

INSERT INTO t (id, value) VALUES ('A',3),('B',5),('C',7) 
ON CONFLICT (id) DO UPDATE SET value = t.value + EXCLUDED.value

Örnek - Where

Şöyle yaparız.

INSERT INTO temp_table
  (source_id, target_id, active) VALUES
  ($1, $2, false)
ON CONFLICT (source_id, target_id) DO UPDATE SET
  active = true
  WHERE similar_channels.source_id = $1
  AND similar_channels.target_id = $2
returning *