SQL từ cơ bản đến nâng cao
Trang chủ / Module 9
✍️ Module 9 / 16 · 6-7 giờ

INSERT/UPDATE/DELETE, UPSERT và thiết kế bảng

Ghi dữ liệu an toàn (RETURNING, UPSERT, MERGE), quy trình sửa/xoá không gây thảm hoạ, và thiết kế bảng với khoá, ràng buộc, chuẩn hoá, migration an toàn.

DMLUPSERTConstraintThiết kế schema
📌 Vì sao module này quan trọng

Đọc sai chỉ cho báo cáo sai; ghi sai làm hỏng dữ liệu thật. Mọi đội đều có một câu chuyện về UPDATE quên WHERE trên production. Phần thứ hai của module — thiết kế bảng — quyết định 80% độ "dễ sống" của hệ thống về sau: ràng buộc đúng giúp dữ liệu không thể sai ngay từ cửa vào, thay vì dựa vào việc code ứng dụng luôn đúng.

🎯 Mục tiêu học tập

  • Dùng INSERT (nhiều dòng, INSERT ... SELECT, RETURNING), UPDATE ... FROM, DELETE ... USING an toàn
  • Viết UPSERT (ON CONFLICT) và MERGE; hiểu 'cập nhật có điều kiện' và tính idempotent
  • Áp dụng quy trình 'SELECT trước, transaction, kiểm tra số dòng' trước khi sửa/xoá dữ liệu thật
  • Thiết kế bảng với PK/FK/UNIQUE/CHECK/NOT NULL đúng, chọn kiểu khoá, chuẩn hoá đến 3NF và migration không downtime

INSERT: một dòng, nhiều dòng, từ truy vấn, và RETURNING

Luôn liệt kê cột tường minh trong INSERT (không dựa vào thứ tự cột của bảng). Gửi nhiều dòng một câu lệnh nhanh hơn rất nhiều so với từng câu.

CREATE TABLE reviews (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  integer NOT NULL REFERENCES products(id),
  customer_id integer NOT NULL REFERENCES customers(id),
  rating      smallint NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment     text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (product_id, customer_id)                 -- mỗi khách chỉ đánh giá 1 lần / sản phẩm
);

INSERT INTO reviews (product_id, customer_id, rating, comment) VALUES
  (1, 1, 5, 'Rất tốt'),
  (1, 2, 4, NULL),
  (7, 1, 3, 'Tạm ổn')
RETURNING id, product_id, rating;
Kết quả
CREATE TABLE
 id | product_id | rating
----+------------+--------
  1 |          1 |      5
  2 |          1 |      4
  3 |          7 |      3
(3 rows)

INSERT 0 3
CREATE TEMP TABLE top_customers (customer_id int, tong_chi numeric);
-- INSERT ... SELECT: nạp kết quả truy vấn vào bảng
INSERT INTO top_customers
SELECT o.customer_id, sum(p.amount)
FROM payments p JOIN orders o ON o.id = p.order_id
GROUP BY o.customer_id
HAVING sum(p.amount) > 300000000
RETURNING *;
Kết quả
CREATE TABLE
 customer_id |   tong_chi
-------------+--------------
          38 | 388803000.00
          26 | 357144600.00
          50 | 362434000.00
           8 | 335643100.00
(4 rows)

INSERT 0 4
💡 RETURNING — lấy lại giá trị sinh tự động

INSERT/UPDATE/DELETE ... RETURNING trả về dòng đã ghi (kèm id sinh tự động, giá trị mặc định, kết quả trigger) trong cùng một lần gọi — thay cho "insert rồi select lại" (hai round-trip và có race condition). Không dùng SELECT max(id) để lấy id vừa tạo — sai khi có nhiều phiên đồng thời.

🧠 Cách chuyên gia — Khoá chính tự sinh: dùng IDENTITY

Chuẩn SQL hiện đại: id bigint GENERATED ALWAYS AS IDENTITY (thay cho serial kiểu cũ — cho phép chặn ghi id thủ công, quyền sở hữu sequence rõ ràng hơn). Dùng bigint từ đầu cho bảng có thể lớn (đổi int → bigint trên bảng 1 tỷ dòng là một dự án). Với id lộ ra ngoài và sinh ở nhiều nơi, cân nhắc UUID v7 (có thứ tự thời gian, thân thiện index). Sequence có "lỗ hổng" (rollback, lỗi vẫn tiêu số) — đừng dùng id làm số thứ tự liên tục như số hoá đơn pháp lý.

UPDATE và DELETE: quy trình không gây thảm hoạ

Thảm hoạ kinh điển: UPDATE orders SET status = 'cancelled'; (quên WHERE) trên production. Quy trình của người cẩn thận:

  1. Viết SELECT với đúng điều kiện WHERE trước, xem số dòng và dữ liệu mẫu.
  2. BEGIN; mở transaction.
  3. Chạy UPDATE/DELETE với cùng WHERE — kiểm tra số dòng trong command tag (ví dụ UPDATE 17) khớp con số ở bước 1.
  4. Kiểm tra lại bằng SELECT; đúng thì COMMIT;, sai thì ROLLBACK;.
-- (Trong bài mỗi khối đã được bọc sẵn trong BEGIN ... ROLLBACK; trên máy bạn hãy tự gõ BEGIN; ở đầu và ROLLBACK/COMMIT; ở cuối)
-- Bước 1: xem trước
SELECT count(*) AS se_bi_anh_huong FROM orders WHERE status = 'pending' AND ordered_at < '2024-06-01';
-- Bước 3: sửa, đọc số dòng từ command tag
UPDATE orders SET status = 'cancelled'
WHERE status = 'pending' AND ordered_at < '2024-06-01';
-- Bước 4: kiểm tra
SELECT status, count(*) FROM orders GROUP BY status ORDER BY status;
Kết quả
 se_bi_anh_huong
-----------------
              14
(1 row)

UPDATE 14
  status   | count
-----------+-------
 cancelled |    54
 delivered |   240
 paid      |    40
 pending   |    26
 shipped   |    40
(5 rows)

UPDATE dựa trên bảng khác dùng FROM (PostgreSQL) — tương đương UPDATE ... JOIN của MySQL/SQL Server; DELETE dùng USING:

-- Giá laptop tăng 10%; đồng bộ lại giá cho các đơn CHƯA xử lý (pending), không đụng đơn đã giao
UPDATE products SET price = price * 1.10 WHERE category_id = 5;

UPDATE order_items oi
SET unit_price = p.price
FROM orders o, products p
WHERE o.id = oi.order_id AND p.id = oi.product_id
  AND o.status = 'pending' AND oi.unit_price <> p.price
RETURNING oi.order_id, oi.product_id, oi.unit_price;
Kết quả
UPDATE 3
 order_id | product_id | unit_price
----------+------------+-------------
        9 |          4 | 24750000.00
       29 |          4 | 24750000.00
       49 |          4 | 24750000.00
       69 |          4 | 24750000.00
       89 |          4 | 24750000.00
      109 |          4 | 24750000.00
      129 |          4 | 24750000.00
      149 |          4 | 24750000.00
      169 |          4 | 24750000.00
... (cắt bớt, chạy trên máy bạn để xem đủ)
-- Xoá các dòng chi tiết thuộc đơn đã huỷ, trả lại số lượng vào kho trong 1 câu lệnh
WITH xoa AS (
  DELETE FROM order_items oi USING orders o
  WHERE o.id = oi.order_id AND o.status = 'cancelled' AND o.id <= 20
  RETURNING oi.product_id, oi.qty
)
UPDATE inventory i SET qty_on_hand = i.qty_on_hand + t.so_luong
FROM (SELECT product_id, sum(qty) AS so_luong FROM xoa GROUP BY product_id) t
WHERE i.product_id = t.product_id
RETURNING i.product_id, i.qty_on_hand;
Kết quả
 product_id | qty_on_hand
------------+-------------
          6 |          33
         13 |          31
         16 |          22
         20 |          26
(4 rows)

UPDATE 4
❌ Lỗi thường gặp — UPDATE/DELETE không có WHERE (hoặc WHERE sai)
  • Luôn có WHERE; nhiều công cụ GUI/psql có chế độ chặn câu lệnh thiếu WHERE — hãy bật.
  • WHERE a = 1 OR b = 2 đã trộn AND/OR không ngoặc sẽ rộng hơn ý bạn (module 2).
  • Trên môi trường thật, dùng tài khoản chỉ đọc cho phân tích; tài khoản ghi chỉ cho dịch vụ và người được phép.
  • Sao lưu/snapshot trước đợt sửa lớn; ghi lại câu lệnh đã chạy và số dòng ảnh hưởng.
❌ Lỗi thường gặp — DELETE hàng loạt một câu lệnh trên bảng khổng lồ

Xoá 100 triệu dòng một câu: giữ khoá dài, sinh WAL khổng lồ, làm lag replica, bảng phình (bloat) vì dòng chết chưa được dọn. Chia theo lô (batch 5.000–50.000 dòng + commit mỗi lô), hoặc nếu xoá dữ liệu theo thời gian thì dùng partitioning rồi DROP PARTITION (tức thì). TRUNCATE xoá sạch bảng nhanh nhưng không có WHERE, lấy khoá mạnh — chỉ khi chắc chắn.

🧠 Cách chuyên gia — Soft delete hay xoá thật?

Soft delete (deleted_at timestamptz) bảo toàn lịch sử và cho phép khôi phục, nhưng: mọi truy vấn phải nhớ WHERE deleted_at IS NULL (dễ quên → lộ dữ liệu "đã xoá"), unique constraint phải thành partial index CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL, và dữ liệu cá nhân vẫn còn (xung đột quyền được xoá/GDPR). Giải pháp phổ biến: view active_xxx che dòng đã xoá, hoặc chuyển dòng sang bảng lưu trữ riêng.

UPSERT: ON CONFLICT và MERGE

UPSERT = "chèn, nếu đã tồn tại thì cập nhật" — thực hiện nguyên tử. Cách "SELECT xem có chưa rồi INSERT/UPDATE" ở code ứng dụng có race condition (hai request cùng thấy "chưa có" rồi cùng chèn).

-- Nhập kho: cộng thêm nếu đã có dòng, tạo mới nếu chưa
INSERT INTO inventory (product_id, qty_on_hand) VALUES (1, 10), (999, 5)
ON CONFLICT (product_id) DO UPDATE
   SET qty_on_hand = inventory.qty_on_hand + EXCLUDED.qty_on_hand
RETURNING product_id, qty_on_hand;
Lỗi PostgreSQL trả về
ERROR:  insert or update on table "inventory" violates foreign key constraint "inventory_product_id_fkey"
DETAIL:  Key (product_id)=(999) is not present in table "products".
-- (sản phẩm 999 không tồn tại nên vi phạm khoá ngoại — lỗi đúng) Chạy lại với sản phẩm hợp lệ:
INSERT INTO inventory (product_id, qty_on_hand) VALUES (1, 10), (2, 5)
ON CONFLICT (product_id) DO UPDATE
   SET qty_on_hand = inventory.qty_on_hand + EXCLUDED.qty_on_hand
RETURNING product_id, qty_on_hand;
Kết quả
 product_id | qty_on_hand
------------+-------------
          1 |          26
          2 |          32
(2 rows)

INSERT 0 2
-- DO NOTHING: bỏ qua dòng trùng (idempotent — chạy lại bao nhiêu lần cũng cùng kết quả)
INSERT INTO inventory (product_id, qty_on_hand) VALUES (1, 99) ON CONFLICT (product_id) DO NOTHING;
SELECT qty_on_hand FROM inventory WHERE product_id = 1;
Kết quả
INSERT 0 0
 qty_on_hand
-------------
          16
(1 row)
CREATE TEMP TABLE kho_dich (product_id int PRIMARY KEY, qty int);
INSERT INTO kho_dich VALUES (1, 100), (2, 50);

-- MERGE (PostgreSQL 15+, chuẩn SQL): một lệnh cho INSERT/UPDATE/DELETE theo điều kiện khớp
MERGE INTO kho_dich t
USING (VALUES (1, 30), (2, 0), (3, 70)) AS s(product_id, qty)
ON t.product_id = s.product_id
WHEN MATCHED AND s.qty = 0 THEN DELETE            -- nguồn báo 0 => xoá
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty  -- có rồi => cộng
WHEN NOT MATCHED THEN INSERT (product_id, qty) VALUES (s.product_id, s.qty);

SELECT * FROM kho_dich ORDER BY product_id;
Kết quả
CREATE TABLE
INSERT 0 2
MERGE 3
 product_id | qty
------------+-----
          1 | 130
          3 |  70
(2 rows)
❌ Lỗi thường gặp — Dùng UPSERT mà không có unique/PK tương ứng

ON CONFLICT (cột) cần một unique index/constraint đúng trên các cột đó, nếu không báo lỗi "no unique or exclusion constraint matching". Ngược lại, thiếu unique constraint thì không có cách nào ngăn trùng — ràng buộc phải do database giữ, đừng tin vào kiểm tra ở ứng dụng.

🧠 Cách chuyên gia — Idempotency

Thiết kế thao tác ghi sao cho chạy lại không gây hại (mạng lỗi → client retry; job chạy lại). INSERT ... ON CONFLICT DO NOTHING với khoá do client sinh (idempotency key), và UPDATE ... SET qty = 5 (đặt giá trị tuyệt đối) idempotent; SET qty = qty + 5 thì không. Biết phân biệt hai loại giúp thiết kế hàng đợi/webhook an toàn.

Thiết kế bảng: kiểu, khoá, ràng buộc

Nguyên tắc: để database bảo vệ dữ liệu, đừng dựa vào ứng dụng. Mỗi quy tắc nghiệp vụ biểu diễn được bằng ràng buộc thì nên biểu diễn.

Ràng buộcBảo đảmVí dụ
PRIMARY KEYĐịnh danh duy nhất, NOT NULLid bigint GENERATED ALWAYS AS IDENTITY
FOREIGN KEYKhông có "con mồ côi"customer_id REFERENCES customers(id)
UNIQUEKhông trùngUNIQUE (product_id, customer_id)
NOT NULLLuôn có giá trịemail text NOT NULL
CHECKĐiều kiện trên giá trịCHECK (price >= 0), CHECK (end_at > start_at)
DEFAULTGiá trị khi không truyềncreated_at timestamptz DEFAULT now()
CREATE TEMP TABLE coupons (
  code        text PRIMARY KEY CHECK (code = upper(code) AND length(code) BETWEEN 4 AND 20),
  discount_pct integer NOT NULL CHECK (discount_pct BETWEEN 1 AND 90),
  starts_at   timestamptz NOT NULL,
  ends_at     timestamptz NOT NULL,
  max_uses    integer CHECK (max_uses > 0),               -- NULL = không giới hạn
  CHECK (ends_at > starts_at)
);
INSERT INTO coupons VALUES ('SALE10', 10, '2024-06-01', '2024-06-30', 100);
INSERT INTO coupons VALUES ('bad',    10, '2024-06-01', '2024-06-30', 100);
Lỗi PostgreSQL trả về
CREATE TABLE
INSERT 0 1
ERROR:  new row for relation "coupons" violates check constraint "coupons_code_check"
DETAIL:  Failing row contains (bad, 10, 2024-06-01 00:00:00+07, 2024-06-30 00:00:00+07, 100).
CREATE TEMP TABLE coupons2 (code text PRIMARY KEY, ends_at timestamptz, starts_at timestamptz, CHECK (ends_at > starts_at));
INSERT INTO coupons2 VALUES ('X', '2024-01-01', '2024-02-01');
Lỗi PostgreSQL trả về
CREATE TABLE
ERROR:  new row for relation "coupons2" violates check constraint "coupons2_check"
DETAIL:  Failing row contains (X, 2024-01-01 00:00:00+07, 2024-02-01 00:00:00+07).

Khoá ngoại và hành vi khi xoá/sửa bản ghi cha

ON DELETEÝ nghĩaDùng khi
NO ACTION / RESTRICT (mặc định)Không cho xoá cha nếu còn conHầu hết trường hợp — an toàn nhất
CASCADEXoá cha thì xoá luôn conCon không có nghĩa nếu thiếu cha (order → order_items)
SET NULLĐặt khoá con = NULLQuan hệ tuỳ chọn (customers.referred_by)
DELETE FROM customers WHERE id = 1;
Lỗi PostgreSQL trả về
ERROR:  update or delete on table "customers" violates foreign key constraint "customers_referred_by_fkey" on table "customers"
DETAIL:  Key (id)=(1) is still referenced from table "customers".
❌ Lỗi thường gặp — Không đặt khoá ngoại 'cho nhanh' / 'cho linh hoạt'

Thiếu FK, dữ liệu mồ côi tích tụ âm thầm (đơn trỏ tới khách đã xoá) và các truy vấn JOIN bắt đầu mất dòng. Chi phí FK về ghi là nhỏ; chi phí dọn dữ liệu bẩn lớn hơn nhiều. Lưu ý: PostgreSQL không tự tạo index cho cột khoá ngoại — hãy tự tạo (module 11), nếu không DELETE ở bảng cha và JOIN sẽ quét toàn bảng con.

❌ Lỗi thường gặp — Mẹo 'VARCHAR(255) cho mọi thứ' và 'một cột lưu nhiều giá trị'

Trong PostgreSQL varchar(255) không nhanh hơn text; giới hạn độ dài chỉ nên đặt khi nghiệp vụ có quy tắc thật. Đừng lưu danh sách trong một cột chuỗi (tags = 'a,b,c') — không ràng buộc, không index, không JOIN được: dùng bảng con (n-n) hoặc kiểu array/jsonb có chủ đích (module 13).

❌ Lỗi thường gặp — EAV (Entity-Attribute-Value) cho mọi thứ

Bảng (entity_id, attr_name, attr_value text) trông "linh hoạt" nhưng mất kiểu dữ liệu, ràng buộc, và truy vấn đơn giản biến thành chục lần self-join. Thuộc tính cố định → cột thật; thuộc tính thưa/đa dạng (như products.attrs) → jsonb + ràng buộc CHECK cần thiết.

Chuẩn hoá, thay đổi schema và migration an toàn

Chuẩn hoá: mỗi sự thật chỉ lưu ở một nơi. 1NF: giá trị nguyên tử (không danh sách trong ô); 2NF: mọi cột phụ thuộc toàn bộ khoá; 3NF: không phụ thuộc bắc cầu (orders.customer_city phụ thuộc customers, không phải đơn → tách). Đạt 3NF là điểm dừng hợp lý cho hệ OLTP. Phi chuẩn hoá có chủ đích (lưu unit_price snapshot ở order_items — vì giá sản phẩm đổi nhưng giá lúc mua không được đổi) là quyết định thiết kế, không phải sai lầm — miễn là có lý do ghi lại. Phần mở rộng (BCNF, sharding, ...) thuộc khoá Database Engineering.

ALTER TABLE trên bảng đang chạy production

Thao tácRủi roCách an toàn
Thêm cột nullable / có DEFAULT hằngGần như tức thì (PG11+)OK
Thêm cột NOT NULL không defaultLỗi nếu bảng có dòngThêm nullable → backfill theo lô → SET NOT NULL
Đổi kiểu cộtViết lại cả bảng, khoá dàiThêm cột mới + đồng bộ + chuyển đổi (expand/contract)
Thêm FK / CHECK trên bảng lớnQuét toàn bảng khi đang giữ khoáADD CONSTRAINT ... NOT VALID rồi VALIDATE CONSTRAINT
CREATE INDEXChặn ghi trong lúc xâyCREATE INDEX CONCURRENTLY
Đổi tên / xoá cộtCode cũ đang chạy vỡ ngayTriển khai theo nhiều bước tương thích ngược
ALTER TABLE products ADD COLUMN weight_g integer;                         -- bước 1: thêm nullable (tức thì)
UPDATE products SET weight_g = 500 WHERE category_id = 6;                -- bước 2: backfill (thực tế: theo lô)
ALTER TABLE products ADD CONSTRAINT chk_weight CHECK (weight_g > 0) NOT VALID;  -- bước 3: ràng buộc, chưa quét dữ liệu cũ
ALTER TABLE products VALIDATE CONSTRAINT chk_weight;                     -- bước 4: kiểm tra dữ liệu cũ, khoá nhẹ hơn
SELECT sku, weight_g FROM products WHERE weight_g IS NOT NULL ORDER BY sku;
Kết quả
ALTER TABLE
UPDATE 4
ALTER TABLE
ALTER TABLE
  sku   | weight_g
--------+----------
 AC-001 |      500
 AC-002 |      500
 AC-003 |      500
 AC-004 |      500
(4 rows)
🧠 Cách chuyên gia — Expand / Contract — migration không downtime
  1. Expand: thêm cấu trúc mới (cột/bảng) tương thích với code cũ; triển khai code ghi vào cả cũ và mới.
  2. Migrate: backfill dữ liệu cũ sang mới theo lô; chuyển đọc sang mới.
  3. Contract: sau khi mọi phiên bản code cũ đã tắt, xoá cấu trúc cũ.

Mỗi bước nhỏ, đảo ngược được. Đặt lock_timeout (ví dụ 3s) khi chạy DDL để migration tự thất bại thay vì xếp hàng chặn toàn bộ truy vấn sau nó.

❌ Lỗi thường gặp — Chạy DDL nặng giờ cao điểm không đặt lock_timeout

ALTER TABLE cần khoá mạnh; nếu một transaction dài đang giữ bảng, ALTER xếp hàng chờ — và mọi truy vấn đến sau cũng xếp hàng phía sau ALTER → cả hệ thống "đứng hình" chỉ vì một migration. SET lock_timeout = '3s' và thử lại ngoài giờ cao điểm.

🏋️ Bài tập thực hành

Thiết kế bảng đặt chỗ phòng họpTrung bình

Thiết kế bảng bookings(room_id, booked_by, starts_at, ends_at) sao cho: thời gian kết thúc phải sau bắt đầu, không ô nào được NULL, và cùng một phòng không được đặt chồng giờ (gợi ý: EXCLUDE USING gist với tstzrange; cần extension btree_gist).

Xem lời giải & giải thích
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TEMP TABLE bookings (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id   integer     NOT NULL,
  booked_by text        NOT NULL,
  starts_at timestamptz NOT NULL,
  ends_at   timestamptz NOT NULL,
  CHECK (ends_at > starts_at),
  EXCLUDE USING gist (room_id WITH =, tstzrange(starts_at, ends_at) WITH &&)
);
INSERT INTO bookings (room_id, booked_by, starts_at, ends_at) VALUES (1, 'An', '2024-06-01 09:00+07', '2024-06-01 10:00+07');
INSERT INTO bookings (room_id, booked_by, starts_at, ends_at) VALUES (1, 'Bình', '2024-06-01 09:30+07', '2024-06-01 10:30+07');
Lỗi PostgreSQL trả về
CREATE EXTENSION
CREATE TABLE
INSERT 0 1
ERROR:  conflicting key value violates exclusion constraint "bookings_room_id_tstzrange_excl"
DETAIL:  Key (room_id, tstzrange(starts_at, ends_at))=(1, ["2024-06-01 09:30:00+07","2024-06-01 10:30:00+07")) conflicts with existing key (room_id, tstzrange(starts_at, ends_at))=(1, ["2024-06-01 09:00:00+07","2024-06-01 10:00:00+07")).

Ràng buộc loại trừ (exclusion constraint) là công cụ độc nhất của PostgreSQL: database từ chối việc đặt chồng giờ kể cả khi hai request đến cùng lúc — điều mà kiểm tra ở ứng dụng không bao giờ đảm bảo được.

UPSERT tính điểm thưởngTrung bình

Có bảng tạm diem(customer_id PK, diem int). Viết câu lệnh cộng thêm điểm cho khách (tạo dòng nếu chưa có) và dùng RETURNING trả về điểm mới. Chạy hai lần liên tiếp và nhận xét: câu lệnh có idempotent không?

Xem lời giải & giải thích
CREATE TEMP TABLE diem (customer_id int PRIMARY KEY, diem int NOT NULL DEFAULT 0);
INSERT INTO diem VALUES (1, 100);
INSERT INTO diem (customer_id, diem) VALUES (1, 20), (2, 20)
ON CONFLICT (customer_id) DO UPDATE SET diem = diem.diem + EXCLUDED.diem
RETURNING *;
Kết quả
CREATE TABLE
INSERT 0 1
 customer_id | diem
-------------+------
           1 |  120
           2 |   20
(2 rows)

INSERT 0 2

Không idempotent: chạy lần hai sẽ cộng thêm 20 nữa. Muốn idempotent cần ghi theo sự kiện có khoá duy nhất (bảng diem_log(order_id PK, ...) với ON CONFLICT DO NOTHING) rồi tính tổng.

Sửa an toànDễ

Hãy mô tả bằng lời (hoặc SQL) quy trình 4 bước để chuyển mọi đơn paid cũ hơn 30 ngày (so với '2024-12-01') sang shipped mà không rủi ro.

Xem lời giải & giải thích
SELECT count(*) AS se_doi FROM orders WHERE status = 'paid' AND ordered_at < date '2024-12-01' - 30;
UPDATE orders SET status = 'shipped', shipped_at = ordered_at + interval '1 day'
WHERE status = 'paid' AND ordered_at < date '2024-12-01' - 30;
SELECT status, count(*) FROM orders GROUP BY status ORDER BY status;
Kết quả
 se_doi
--------
     39
(1 row)

UPDATE 39
  status   | count
-----------+-------
 cancelled |    40
 delivered |   240
 paid      |     1
 pending   |    40
 shipped   |    79
(5 rows)

SELECT cùng WHERE trước (ghi lại số dòng), UPDATE trong transaction (xem command tag UPDATE n có khớp n đó không), kiểm tra lại rồi mới COMMIT. Chú ý cột phụ thuộc (shipped_at) phải cập nhật cùng lúc để dữ liệu nhất quán.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Luôn SELECT trước, dùng transaction, kiểm tra số dòng khi UPDATE/DELETE dữ liệu thật
  • Viết được UPSERT/MERGE và giải thích tính idempotent
  • Thiết kế được bảng với PK/FK/UNIQUE/CHECK/NOT NULL phù hợp, không dựa vào ứng dụng để giữ toàn vẹn
  • Biết các bước migration an toàn: NOT VALID/VALIDATE, CONCURRENTLY, expand/contract, lock_timeout