RETURNING va upsert

INSERT, UPDATE va DELETE ni siz bilasiz. Bu bo'limda PostgreSQL ularga qo'shgan ikkita narsani ko'ramiz - va ikkalasi ham kundalik ishni sezilarli soddalashtiradi.

CREATE TABLE oquvchi (
    id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism     text NOT NULL,
    email   text UNIQUE NOT NULL,
    ball    int NOT NULL DEFAULT 0,
    faol    boolean NOT NULL DEFAULT true
);

INSERT INTO oquvchi (ism, email, ball) VALUES
    ('Husanboy', '[email protected]', 85),
    ('Malika',   '[email protected]',   92),
    ('Nodira',   '[email protected]',   78),
    ('Aziza',    '[email protected]',    64);

RETURNING - natijani darhol olish #

Odatdagi INSERT hech narsa qaytarmaydi. Yangi qatorning id sini bilish uchun ikkinchi so'rov yuborish kerak bo'lardi.

PostgreSQL da esa:

INSERT INTO oquvchi (ism, email, ball)
VALUES ('Kamola', '[email protected]', 88)
RETURNING id, ism, ball;

 id |  ism   | ball
----+--------+------
  5 | Kamola |   88
(1 row)

INSERT 0 1

Bitta so'rov - va yangi id qo'lingizda.

RETURNING o'chirishda ayniqsa foydali - nima o'chganini ko'rasiz:

DELETE FROM oquvchi WHERE ball < 70
RETURNING id, ism, ball;

 id |  ism  | ball
----+-------+------
  4 | Aziza |   64
(1 row)

DELETE 1

UPDATE da esa eski va yangi qiymatni solishtirish mumkin:

UPDATE oquvchi SET ball = ball + 5 WHERE ism = 'Nodira'
RETURNING ism, ball - 5 AS eski_ball, ball AS yangi_ball;

  ism   | eski_ball | yangi_ball
--------+-----------+------------
 Nodira |        78 |         83
(1 row)

UPDATE 1

ON CONFLICT - upsert #

Klassik vazifa: "qator bo'lsa yangila, bo'lmasa qo'sh".

Oddiy yo'l bilan bu uch qadam bo'lardi: tekshir, keyin qo'sh yoki yangila. Va yana poyga holati paydo bo'ladi.

PostgreSQL da bu bitta so'rov:

INSERT INTO oquvchi (ism, email, ball)
VALUES ('Malika', '[email protected]', 99)
ON CONFLICT (email) DO UPDATE SET ball = EXCLUDED.ball
RETURNING id, ism, ball;

 id |  ism   | ball
----+--------+------
  2 | Malika |   99
(1 row)

INSERT 0 1

EXCLUDED - bu qo'shilmoqchi bo'lgan, lekin to'qnashuv sababli qabul qilinmagan qator. Ya'ni "yangi qiymatni ol".

Yangi email bilan esa oddiy qo'shish bo'ladi:

INSERT INTO oquvchi (ism, email, ball)
VALUES ('Dilnoza', '[email protected]', 71)
ON CONFLICT (email) DO UPDATE SET ball = EXCLUDED.ball
RETURNING id, ism, ball;

 id |   ism   | ball
----+---------+------
  5 | Dilnoza |   71
(1 row)

INSERT 0 1

Bir xil so'rov ikki xil ish bajardi - va ikkalasida ham natija darhol qaytdi.

INSERT INTO oquvchi (ism, email) VALUES ('Yangi', '[email protected]')
ON CONFLICT DO NOTHING;

UPDATE ... FROM #

Bitta jadvalni boshqa jadvaldagi ma'lumot asosida yangilash kerak bo'lsa:

CREATE TABLE bonus (email text, qoshimcha int);
INSERT INTO bonus VALUES ('[email protected]', 10), ('[email protected]', 5);

UPDATE oquvchi o
SET ball = o.ball + b.qoshimcha
FROM bonus b
WHERE o.email = b.email
RETURNING o.ism, o.ball;

CREATE TABLE
INSERT 0 2
   ism    | ball
----------+------
 Husanboy |   95
 Nodira   |   83
(2 rows)

UPDATE 2

Bu JOIN ga o'xshaydi, lekin yangilash uchun. Ko'p bazalarda buni qilish ancha noqulay.

DELETE ... USING #

O'chirishda ham xuddi shunday imkoniyat bor:

CREATE TABLE qora_royxat (email text);
INSERT INTO qora_royxat VALUES ('[email protected]');

DELETE FROM oquvchi o
USING qora_royxat q
WHERE o.email = q.email
RETURNING o.ism, o.email;

CREATE TABLE
INSERT 0 1
  ism  |     email
-------+----------------
 Aziza | [email protected]
(1 row)

DELETE 1

Xulosa #

• RETURNING INSERT, UPDATE va DELETE natijasini darhol qaytaradi.
• U ikkinchi so'rovni ham, u bilan bog'liq poyga holatini ham yo'q qiladi.
• ON CONFLICT "bo'lsa yangila, bo'lmasa qo'sh" vazifasini bitta amalda bajaradi.
• EXCLUDED - to'qnashuv sababli qabul qilinmagan yangi qator.
• DO NOTHING jimgina o'tkazib yuboradi - lekin belgi bermaydi.
• UPDATE ... FROM boshqa jadvaldagi ma'lumot asosida yangilaydi.
• DELETE ... USING xuddi shu imkoniyatni o'chirishga beradi.
• "Avval tekshir, keyin yoz" naqshi ko'p foydalanuvchida buziladi.
• WHERE siz UPDATE butun jadvalga tegadi - BEGIN bilan himoyalaning.
• RETURNING ni UPDATE bilan birga ishlatish - o'zgarishni tekshirishning eng tez yo'li.

Keyingi bo'limda SELECT ning PostgreSQL ga xos kuchli tomonlarini ko'ramiz.