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
Postgres 9.5 and above FINALLY supports UPSERT (otherwise known as: INSERT ... ON CONFLICT DO ).
INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)
ON CONFLICT conflict_target conflict_action;
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
INSERT INTO "public"."post_details" (
"id",
"created_by",
"created_on")
VALUES (
1,
'Alice',
TIMESTAMP '2017-11-06 16:42:37.692')
ON CONFLICT DO NOTHING2.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 ...;
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
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
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 *
