SQL پایه به سبک پستگرس: از SELECT تا RETURNING

چهار فرمان، با جزئیاتی که فرق ایجاد می‌کند

SELECT، INSERT، UPDATE و DELETE را احتمالاً می‌شناسید؛ این درس روی جزئیاتی تمرکز دارد که پستگرس را از بقیه جدا می‌کند. یک جدول ساده برای تمرین:

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name  text NOT NULL,
  city       text,
  mobile     text,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (full_name, city, mobile) VALUES
  ('فرش صدرا', 'کاشان', '09131234567'),
  ('بازرگانی نیک‌نام', 'تهران', NULL),
  ('گالری مهرآذین', 'آران و بیدگل', '09121112233')
RETURNING id, full_name;

RETURNING ردیف‌های درج‌شده، به‌روزشده یا حذف‌شده را همان‌جا برمی‌گرداند؛ دیگر لازم نیست برای گرفتن id یک SELECT جدا بزنید (کاری که در MySQL با LAST_INSERT_ID می‌کنید).

فیلتر، NULL و مرتب‌سازی

SELECT id, full_name, coalesce(mobile, 'ندارد') AS mobile
FROM customers
WHERE city ILIKE 'کاشان%'            -- ILIKE: بدون حساسیت به بزرگی و کوچکی حروف لاتین
  AND mobile IS DISTINCT FROM '09130000000'
ORDER BY created_at DESC NULLS LAST, id
LIMIT 20;

مقایسه با NULL همیشه NULL است، نه true یا false؛ پس mobile <> 'x' ردیف‌هایی که mobile ندارند را حذف می‌کند. IS DISTINCT FROM با NULL مثل یک مقدار عادی رفتار می‌کند.

UPDATE و DELETE با جدول دیگر

UPDATE orders o
SET    status = 'cancelled'
FROM   customers c
WHERE  o.customer_id = c.id AND c.city = 'تهران' AND o.status = 'draft'
RETURNING o.id;

DELETE FROM order_items oi
USING  orders o
WHERE  oi.order_id = o.id AND o.status = 'cancelled';

برای انتقال امن داده، RETURNING را با CTE ترکیب کنید: ردیف‌ها در یک دستور از جدول اصلی حذف و در آرشیو درج می‌شوند (در فصل ۴ مفصل‌تر):

WITH moved AS (
  DELETE FROM orders WHERE created_at < now() - interval '3 years' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

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

• در پستگرس NULLها در مرتب‌سازی صعودی آخر می‌آیند؛ در MySQL اول. اگر گزارشی را از MySQL منتقل می‌کنید، NULLS FIRST/LAST را صریح بنویسید.
• صفحه‌بندی با OFFSET بزرگ کند است، چون همه‌ی ردیف‌های قبلی خوانده و دور ریخته می‌شوند. صفحه‌بندی keyset سریع است: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20؛ مقایسه‌ی ردیفی (tuple) در پستگرس از ایندکس چندستونی استفاده می‌کند.
• ORDER BY بدون ستون یکتا (مثلاً فقط created_at) صفحه‌بندی ناپایدار می‌دهد؛ همیشه id را به‌عنوان ستون آخر اضافه کنید.
• قبل از UPDATE دستی روی سرور تولید، BEGIN; بزنید، تعداد ردیف‌های گزارش‌شده را ببینید و بعد COMMIT یا ROLLBACK کنید؛ psql پیام UPDATE 3812 را درست قبل از فاجعه نشان می‌دهد.
• TRUNCATE orders RESTART IDENTITY CASCADE جدول و جدول‌های وابسته را خالی و شمارنده را صفر می‌کند؛ برخلاف MySQL، TRUNCATE در پستگرس تراکنشی است و ROLLBACK می‌شود.