upsert با ON CONFLICT، MERGE، DISTINCT ON و generate_series

چهار ابزار که کد برنامه را کوتاه می‌کنند

INSERT ... ON CONFLICT (upsert)

مسئله‌ی کلاسیک: موجودی انبار نخ را به‌روز کنید؛ اگر ردیف نیست بسازید، اگر هست اضافه کنید. «اول SELECT، بعد INSERT یا UPDATE» در بار هم‌زمان شکست می‌خورد. ON CONFLICT این کار را اتمی انجام می‌دهد:

CREATE TABLE yarn_stock (
  warehouse_id int,
  yarn_code    text,
  kg           numeric(12,3) NOT NULL,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (warehouse_id, yarn_code)
);

INSERT INTO yarn_stock AS s (warehouse_id, yarn_code, kg)
VALUES (1, 'AC-RED-12', 250)
ON CONFLICT (warehouse_id, yarn_code)
DO UPDATE SET kg = s.kg + EXCLUDED.kg, updated_at = now()
WHERE s.kg + EXCLUDED.kg >= 0
RETURNING *, (xmax = 0) AS inserted;

EXCLUDED ردیفی است که قصد درجش را داشتید. هدف تعارض باید یک UNIQUE یا PRIMARY KEY واقعی (یا ایندکس یکتای منطبق) باشد. DO NOTHING هم برای «اگر هست، کاری نکن» کاربرد دارد.

MERGE (نسخه‌ی 15 به بعد)

MERGE استاندارد SQL است و برای همگام‌سازی یک جدول با جدول staging خواناتر است؛ حذف را هم پوشش می‌دهد. از نسخه‌ی 17 RETURNING و تابع merge_action() هم دارد:

MERGE INTO designs d
USING staging_designs s ON d.code = s.code
WHEN MATCHED AND s.deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET title = s.title, colors = s.colors
WHEN NOT MATCHED AND NOT s.deleted THEN INSERT (code, title, colors) VALUES (s.code, s.title, s.colors)
RETURNING merge_action(), d.code;

DISTINCT ON: «آخرین ردیف هر گروه»

-- آخرین سفارش هر مشتری
SELECT DISTINCT ON (customer_id) customer_id, id, total_rial, created_at
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;

ستون‌های DISTINCT ON باید اول ORDER BY بیایند؛ باقی ORDER BY تعیین می‌کند کدام ردیف گروه نگه داشته شود.

generate_series: پر کردن جاهای خالی

-- فروش روزانه‌ی ۳۰ روز اخیر، حتی روزهایی که فروش صفر بوده
SELECT d::date AS day, coalesce(sum(o.total_rial), 0) AS sales
FROM generate_series(current_date - 29, current_date, interval '1 day') AS d
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d ORDER BY d;

-- ۱۰۰ هزار ردیف داده‌ی آزمایشی
INSERT INTO customers (full_name, city)
SELECT 'مشتری ' || i, (ARRAY['کاشان','تهران','مشهد','تبریز'])[1 + i % 4]
FROM generate_series(1, 100000) AS i;

نکته‌هایی که کمتر کسی می‌داند

• ترفند RETURNING (xmax = 0) AS inserted نشان می‌دهد ردیف تازه درج شده یا به‌روز شده است؛ رسمی مستند نیست اما سال‌هاست همه از آن استفاده می‌کنند.
• اگر یک دستور INSERT چندردیفی دو ردیف با کلید یکسان داشته باشد، خطای ON CONFLICT DO UPDATE command cannot affect row a second time می‌گیرید؛ داده را قبلاً با DISTINCT ON یکتا کنید.
• هر upsert حتی وقتی به UPDATE ختم شود یک عدد از sequence مصرف می‌کند؛ جدول‌هایی که بیشتر upsert می‌خورند شناسه‌های پرحفره دارند و با int زودتر به سقف می‌رسند.
• MERGE برخلاف ON CONFLICT در برابر درج هم‌زمان مقاوم نیست و ممکن است خطای کلید تکراری بدهد؛ برای بار هم‌زمان بالا ON CONFLICT امن‌تر است.
• برای اینکه DISTINCT ON روی جدول بزرگ سریع باشد، ایندکس (customer_id, created_at DESC) بسازید؛ اگر تعداد گروه‌ها کم و ردیف‌ها زیاد است، LATERAL با LIMIT 1 (فصل ۴) سریع‌تر است.