افزودن، ویرایش و حذف

توی این درس یاد می‌گیری داده را تغییر بدهی: با INSERT سطر اضافه کنی و با RETURNING همان لحظه ببینی چه ذخیره شد، با UPDATE مقدارها را عوض کنی (حتی با محاسبه، مثل «همه‌ی کتاب‌ها ۱۰٪ گران‌تر»)، و با DELETE سطر حذف کنی. مهم‌تر از همه، یاد می‌گیری چطور این کارها را امن انجام بدهی: WHERE که اگر جا بیفتد کل جدول عوض می‌شود، و تراکنش (BEGIN، COMMIT، ROLLBACK) که به تو اجازه می‌دهد قبل از قطعی کردن، نتیجه را ببینی و اگر اشتباه بود برگردانی. در آخر «اگر بود به‌روز کن، وگرنه اضافه کن» را با ON CONFLICT یاد می‌گیری.

مسئله: یک خط، کل جدول

خواندن داده هیچ‌وقت چیزی را خراب نمی‌کند. ولی UPDATE و DELETE می‌کنند، و دیتابیس سطل زباله ندارد: سطری که حذف شد، رفت. معروف‌ترین فاجعه‌ی دیتابیس یک خط است: UPDATE products SET price = 0; که کسی می‌خواست بنویسد ... WHERE id = 7 و Enter را زود زد.

سناریوی واقعی

فرض کن توی یک شرکت، پشتیبانی از تو می‌خواهد قیمت یک محصول را اصلاح کنی. با عجله می‌نویسی UPDATE products SET price = 450000 و Enter؛ WHERE جا افتاده و قیمت همه‌ی محصول‌های سایت ۴۵۰ هزار تومان شده. اگر قبلش BEGIN زده بودی، خروجی UPDATE 11834 (به‌جای UPDATE 1) را می‌دیدی، ROLLBACK می‌زدی و هیچ‌کس هیچ‌وقت نمی‌فهمید. بدون آن، تا بکاپ دیشب برگردانده شود، سایت ساعت‌ها با قیمت غلط می‌فروشد.

تشبیه: پیش‌نویس و امضا

تراکنش مثل نوشتن یک قرارداد است: BEGIN یعنی «پیش‌نویس را شروع کن». هر تغییری که می‌دهی فعلاً روی پیش‌نویس است؛ خودت می‌بینی‌اش ولی برای بقیه هنوز رسمی نیست. COMMIT یعنی امضا: همه‌ی تغییرها با هم رسمی می‌شوند. ROLLBACK یعنی «پیش‌نویس را پاره کن»: انگار هیچ‌کدام اتفاق نیفتاده. و نکته‌ی مهم: یا همه امضا می‌شوند یا هیچ‌کدام؛ نمی‌شود نصف قرارداد را امضا کرد.

بدون BEGIN هر دستور فوراً قطعی می‌شود؛ با BEGIN تغییرها تا COMMIT موقت‌اند و ROLLBACK همه را برمی‌گرداند.

مثال‌های عملی

قبل از شروع، جدول را تازه کن (فایل درس قبل):

psql shop -f ~/products.sql
psql shop

مثال ۱: INSERT با RETURNING

INSERT INTO products (name, category, price, stock)
VALUES ('مانیتور 24 اینچ', 'لوازم جانبی', 6500000, 2)
RETURNING id, name, added_on;

خروجی

 id |      name       |  added_on
----+-----------------+------------
 13 | مانیتور 24 اینچ | 2026-10-21
(1 row)

INSERT 0 1

RETURNING (مخصوص PostgreSQL و چند دیتابیس دیگر) سطری را که واقعاً ذخیره شد برمی‌گرداند، با مقدارهایی که دیتابیس خودش گذاشت: id خودکار و added_on که پیش‌فرضش تاریخ امروز است. برنامه‌ها معمولاً همین id را لازم دارند (مثلاً برای نشان دادن «سفارش شماره‌ی ۱۳ ثبت شد»).

مثال ۲: UPDATE

UPDATE products SET stock = 25 WHERE name = 'هدفون';
SELECT name, stock FROM products WHERE name = 'هدفون';

خروجی

UPDATE 1
 name  | stock
-------+-------
 هدفون |    25
(1 row)

UPDATE جدول SET ستون = مقدار WHERE شرط. UPDATE 1 یعنی «یک سطر عوض شد». همیشه این عدد را نگاه کن: اگر انتظار یک سطر داشتی و عدد دیگری دیدی، چیزی اشتباه است.

مقدار جدید می‌تواند محاسبه باشد، و می‌شود چند ستون را با هم عوض کرد:

UPDATE products SET price = price * 110 / 100 WHERE category = 'کتاب' RETURNING name, price;
UPDATE products SET discount = NULL, stock = stock + 10 WHERE id = 9 RETURNING name, stock, discount;

خروجی

    name     | price
-------------+--------
 کتاب پایتون | 352000
 کتاب لینوکس | 308000
 کتاب داکر   | 385000
(3 rows)

UPDATE 3
    name     | stock | discount
-------------+-------+----------
 SSD 512 گیگ |    17 |
(1 row)

UPDATE 1

price = price * 110 / 100 یعنی «قیمت فعلی همین سطر ضربدر ۱٫۱». RETURNING با UPDATE هم کار می‌کند و مقدارهای بعد از تغییر را نشان می‌دهد. اگر هیچ سطری با شرط جور نباشد:

UPDATE products SET stock = 99 WHERE name = 'ناموجود';

خروجی

UPDATE 0

خطا نیست؛ فقط هیچ سطری عوض نشد. (برای برنامه‌ها مهم است: «صفر سطر» ممکن است یعنی شناسه‌ی اشتباه.)

مثال ۳: DELETE

DELETE FROM products WHERE stock = 0 RETURNING id, name;
SELECT count(*) FROM products;

خروجی

 id |     name
----+--------------
 12 | سوئیچ 8 پورت
  2 | کتاب لینوکس
(2 rows)

DELETE 2
 count
-------
    11
(1 row)

دو کالای تمام‌شده حذف شدند (هدفون در مثال ۲ موجودی گرفته بود). count(*) تعداد سطرهاست (درس بعد). یک عادت حرفه‌ای: قبل از هر DELETE، همان WHERE را با SELECT امتحان کن: SELECT id, name FROM products WHERE stock = 0;. اگر فهرست همان بود که فکر می‌کردی، بعد SELECT را به DELETE تبدیل کن.

مثال ۴: تور ایمنی، BEGIN و ROLLBACK

حالا عمداً بدترین اشتباه را، داخل یک تراکنش، انجام می‌دهیم:

BEGIN;
UPDATE products SET price = 0;
SELECT name, price FROM products LIMIT 3;
ROLLBACK;
SELECT name, price FROM products ORDER BY id LIMIT 3;

خروجی

BEGIN
UPDATE 11
      name      | price
----------------+-------
 ماوس بی‌سیم     |     0
 کیبورد مکانیکی |     0
 هارد اکسترنال  |     0
(3 rows)

ROLLBACK
    name     | price
-------------+--------
 کتاب پایتون | 352000
 کتاب داکر   | 385000
 ماوس بی‌سیم  | 450000
(3 rows)

UPDATE 11 (به‌جای یک) زنگ خطر بود؛ SELECT تأییدش کرد؛ ROLLBACK همه‌چیز را برگرداند. داخل تراکنش، فقط خود تو تغییرها را می‌بینی؛ برنامه‌ها و کاربرهای دیگر تا COMMIT همان داده‌ی قبلی را می‌بینند. (و ببین: ترتیب سطرها بعد از UPDATE عوض شد. باز هم: بدون ORDER BY ترتیب تضمین‌شده نیست.)

قانون شخصی پیشنهادی

روی هر دیتابیسی که داده‌ی واقعی دارد، هر UPDATE و DELETE را با BEGIN شروع کن، عدد سطرها را ببین، با SELECT نتیجه را بررسی کن، و فقط بعد COMMIT. چند ثانیه وقت در برابر چند ساعت بازیابی.

مثال ۵: یا همه یا هیچ

مهم‌ترین کار تراکنش: چند تغییر وابسته با هم انجام شوند یا هیچ‌کدام. مثلاً جابه‌جایی موجودی از یک انبار به دیگری (کم کردن از یکی و اضافه کردن به دیگری):

BEGIN;
UPDATE products SET stock = stock - 2 WHERE id = 4;
UPDATE products SET stock = stock + 2 WHERE id = 8;
COMMIT;
SELECT id, name, stock FROM products WHERE id IN (4, 8) ORDER BY id;

خروجی

BEGIN
UPDATE 1
UPDATE 1
COMMIT
 id |    name    | stock
----+------------+-------
  4 | ماوس بی‌سیم |    28
  8 | فلش 64 گیگ |    52
(2 rows)

اگر وسط این دو UPDATE برق می‌رفت یا خطایی رخ می‌داد، بدون تراکنش دو کالا از انبار «گم» می‌شدند. با تراکنش، PostgreSQL تضمین می‌کند یا هر دو ثبت شوند یا هیچ‌کدام.

حالا ببین وقتی وسط تراکنش خطا رخ دهد چه می‌شود:

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO products (name, category, price) VALUES ('بدون قیمت', 'کتاب', -1);
SELECT stock FROM products WHERE id = 1;
COMMIT;

خروجی

BEGIN
UPDATE 1
ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (14, بدون قیمت, کتاب, -1, 0, null, 2026-10-21).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

قیمت منفی قید CHECK را نقض کرد. از آن لحظه تراکنش خراب (aborted) است: هر دستوری (حتی SELECT) رد می‌شود، و COMMIT در عمل ROLLBACK کرد (ببین psql چه چاپ کرد). یعنی UPDATE اول هم برگشت. این دقیقاً همان «یا همه یا هیچ» است.

⚡ بررسی سریع

بعد از BEGIN، دستوری خطا داد. حالا COMMIT بزنی چه می‌شود؟

تغییرهای قبل از خطا ثبت می‌شوندکل تراکنش برگردانده می‌شود (ROLLBACK)خطا نادیده گرفته می‌شوددیتابیس قفل می‌شود

مثال ۶: «اگر بود به‌روز کن، وگرنه اضافه کن»، ON CONFLICT

برای این مثال اسم محصول را یکتا کن (ایندکس یکتا را در درس ایندکس کامل می‌بینی):

CREATE UNIQUE INDEX products_name_key ON products (name);
INSERT INTO products (name, category, price, stock) VALUES ('ماوس بی‌سیم', 'لوازم جانبی', 450000, 5);

خروجی

CREATE INDEX
ERROR:  duplicate key value violates unique constraint "products_name_key"
DETAIL:  Key (name)=(ماوس بی‌سیم) already exists.

ماوس از قبل هست. فرض کن ۵ عدد ماوس جدید رسیده و می‌خواهی «اگر هست، به موجودی‌اش اضافه کن؛ وگرنه سطر جدید بساز». به این upsert می‌گویند:

INSERT INTO products (name, category, price, stock)
VALUES ('ماوس بی‌سیم', 'لوازم جانبی', 450000, 5)
ON CONFLICT (name) DO UPDATE SET stock = products.stock + EXCLUDED.stock
RETURNING name, stock;

خروجی

    name    | stock
------------+-------
 ماوس بی‌سیم |    33
(1 row)

INSERT 0 1

ON CONFLICT (name) یعنی «اگر به‌خاطر تکراری بودن name نشد». EXCLUDED سطری است که می‌خواستی وارد کنی (۵ تا) و products.stock موجودی فعلی (۲۸). اگر فقط می‌خواهی تکراری‌ها بی‌صدا رد شوند: ON CONFLICT (name) DO NOTHING (خروجی: INSERT 0 0).

مثال ۷: قیدها از UPDATE هم محافظت می‌کنند

UPDATE products SET price = -5 WHERE id = 10;

خروجی

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (10, کابل شبکه, شبکه, -5, 120, null, 2026-01-05).

همان قید CHECK (price >= 0) که در CREATE TABLE گذاشتیم. هر قانونی که در دیتابیس تعریف شود، برای هر دستوری و از هر برنامه‌ای اجرا می‌شود.

مثال ۸: خالی کردن کل جدول

DELETE FROM products;
TRUNCATE products;

هر دو همه‌ی سطرها را پاک می‌کنند. فرق: DELETE سطر به سطر حذف می‌کند (کند روی جدول بزرگ، ولی می‌شود با WHERE محدودش کرد و RETURNING دارد)؛ TRUNCATE کل جدول را یک‌جا خالی می‌کند (خیلی سریع، بدون WHERE). هر دو داخل BEGIN قابل‌برگشت‌اند. بعد از این مثال، psql shop -f ~/products.sql را بزن تا داده برگردد.

پشت پرده: تراکنش و autocommit

در psql (و بیشتر کتابخانه‌ها)، اگر BEGIN نزنی، هر دستور خودش یک تراکنش کوچک است که بلافاصله COMMIT می‌شود (autocommit). برای همین UPDATE بی‌WHERE بدون تراکنش فوراً و برای همیشه قطعی است.

PostgreSQL قبل از اینکه COMMIT را تأیید کند، تغییر را در WAL (write-ahead log، یک دفتر ثبت ترتیبی روی دیسک) می‌نویسد. اگر برق برود، هنگام روشن شدن دوباره WAL را می‌خواند: تراکنش‌هایی که COMMIT شده بودند کامل‌اند و بقیه انگار هرگز نبوده‌اند. به این ویژگی‌ها، در کنار جدا بودن تراکنش‌های هم‌زمان از هم، ACID می‌گویند و دلیل اصلی است که پول، سفارش و موجودی را در دیتابیس نگه می‌داریم، نه در فایل.

DELETE سطر به سطر و قابل‌فیلتر است؛ TRUNCATE کل جدول را یک‌جا خالی می‌کند. هر دو داخل تراکنش برگشت‌پذیرند.

جدول‌های مرجع

دستور	شکل
افزودن	INSERT INTO t (a, b) VALUES (1, 2), (3, 4) RETURNING *;
ویرایش	UPDATE t SET a = 1, b = b + 1 WHERE id = 5;
حذف	DELETE FROM t WHERE id = 5;
upsert	INSERT ... ON CONFLICT (col) DO UPDATE SET ...;
نادیده گرفتن تکراری	INSERT ... ON CONFLICT (col) DO NOTHING;
خالی کردن جدول	TRUNCATE t;

تراکنش	کار
BEGIN;	شروع
COMMIT;	قطعی کردن همه
ROLLBACK;	برگرداندن همه

خروجی psql	معنی
INSERT 0 3	۳ سطر اضافه شد
UPDATE 1 / DELETE 0	یک سطر عوض شد / هیچ سطری حذف نشد
current transaction is aborted	تراکنش خطا داده؛ فقط ROLLBACK

اشتباهات رایج

۱) UPDATE یا DELETE بدون WHERE

همه‌ی سطرها عوض یا حذف می‌شوند و psql هیچ سؤالی نمی‌پرسد. راه‌حل: BEGIN اول، عدد سطرها را ببین، اول SELECT با همان WHERE.

۲) فراموش کردن COMMIT

اگر BEGIN زدی و psql را بستی، تغییرها برمی‌گردند. بدتر: تا وقتی تراکنش باز است، ممکن است سطرهایی که عوض کرده‌ای برای بقیه قفل بمانند و برنامه‌ی دیگری منتظر بماند. راه‌حل: تراکنش را کوتاه نگه دار و همیشه با COMMIT یا ROLLBACK تمامش کن. (پرامپت psql داخل تراکنش shop=*> است؛ آن * یادآوری خوبی است.)

۳) ادامه دادن بعد از خطا در تراکنش

مثال ۵: بعد از خطا، هر دستوری current transaction is aborted می‌دهد. راه‌حل: ROLLBACK و از اول.

۴) شرط WHERE با NULL

UPDATE products SET discount = 5 WHERE discount = NULL; روی هیچ سطری اثر نمی‌کند (UPDATE 0). راه‌حل: WHERE discount IS NULL (درس قبل).

۵) فکر کردن اینکه DELETE شمارنده را برمی‌گرداند

بعد از حذف سطر ۱۲، سطر بعدی ۱۳ می‌گیرد، نه ۱۲. شناسه‌ها برای یکتا بودناند، نه پشت‌سرهم بودن؛ هرگز روی «بدون فاصله بودن» آن‌ها حساب نکن.

تمرین

تمرینآسان

یک محصول جدید (پد ماوس، دسته‌ی لوازم جانبی، ۱۲۰ هزار تومان، ۴۰ عدد) اضافه کن و با RETURNING شناسه‌اش را بگیر. بعد قیمتش را به ۹۹ هزار تومان کاهش بده و نتیجه را نشان بده.

دیدن جواب

INSERT INTO products (name, category, price, stock)
VALUES ('پد ماوس', 'لوازم جانبی', 120000, 40)
RETURNING id;
UPDATE products SET price = 99000 WHERE name = 'پد ماوس' RETURNING name, price;

خروجی (روی جدول تازه)

 id
----
 13
(1 row)

INSERT 0 1
  name   | price
---------+-------
 پد ماوس | 99000
(1 row)

UPDATE 1

بهتر است در UPDATE از شناسه استفاده کنی (WHERE id = 13)، چون اسم ممکن است تکراری باشد.

تمرینمتوسط

تمرین اصلی: داخل یک تراکنش، همه‌ی کالاهای دسته‌ی «ذخیره‌سازی» را ۵٪ ارزان‌تر کن، با SELECT نتیجه را ببین، و بعد تصمیم بگیر: اگر هیچ قیمتی زیر ۲۵۰ هزار تومان نرفته، COMMIT کن؛ وگرنه ROLLBACK.

دیدن جواب

BEGIN;
UPDATE products SET price = price * 95 / 100 WHERE category = 'ذخیره‌سازی';
SELECT name, price FROM products WHERE category = 'ذخیره‌سازی' ORDER BY price;
COMMIT;

خروجی (روی جدول تازه)

BEGIN
UPDATE 3
     name      |  price
---------------+---------
 فلش 64 گیگ    |  275500
 SSD 512 گیگ   | 2612500
 هارد اکسترنال | 3040000
(3 rows)

COMMIT

ارزان‌ترین (فلش) ۲۷۵ هزار و ۵۰۰ تومان شد، بالای حد، پس COMMIT. نکته: price * 95 / 100 روی ستون INTEGER تقسیم صحیح است و اعشار را می‌بُرد؛ برای پول، نوع NUMERIC یا نگه داشتن قیمت به ریال این مشکل را ندارد.

تمرینسخت

جدول کوچک stock_log بساز (id، product_id، change، at با پیش‌فرض now()) و یک «فروش» شبیه‌سازی کن: داخل یک تراکنش، از موجودی کالای ۱ سه عدد کم کن و یک سطر با change = -3 در stock_log بنویس. ثابت کن اگر فروش بیش از موجودی باشد (مثلاً ۱۰۰ عدد)، با قید CHECK (stock >= 0) هیچ‌کدام از دو تغییر ثبت نمی‌شود.

دیدن جواب

ALTER TABLE products ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);
CREATE TABLE stock_log (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    change INTEGER NOT NULL,
    at TIMESTAMPTZ NOT NULL DEFAULT now()
);

BEGIN;
INSERT INTO stock_log (product_id, change) VALUES (1, -3);
UPDATE products SET stock = stock - 3 WHERE id = 1;
COMMIT;

BEGIN;
INSERT INTO stock_log (product_id, change) VALUES (1, -100);
UPDATE products SET stock = stock - 100 WHERE id = 1;
COMMIT;

SELECT stock FROM products WHERE id = 1;
SELECT product_id, change FROM stock_log;

خروجی (بخش‌های مهم)

COMMIT
BEGIN
INSERT 0 1
ERROR:  new row for relation "products" violates check constraint "stock_not_negative"
DETAIL:  Failing row contains (1, کتاب پایتون, کتاب, 320000, -91, 10, 2026-03-10).
ROLLBACK
 stock
-------
     9
(1 row)

 product_id | change
------------+--------
          1 |     -3
(1 row)

فروش اول (۳ عدد) کامل ثبت شد: موجودی ۱۲ به ۹ رسید و یک سطر لاگ. فروش دوم با قید رد شد و چون در یک تراکنش بود، سطر لاگش هم برگشت؛ لاگ و موجودی هیچ‌وقت با هم ناهماهنگ نمی‌شوند. (now() زمان فعلی است.)

آزمونک

آزمونک

1. UPDATE products SET price = 0; (بدون WHERE) چه می‌کند؟

   خطا می‌دهدقیمت همه‌ی سطرها را صفر می‌کندفقط سطر اول راهیچ

2. RETURNING در INSERT چه فایده‌ای دارد؟

   سرعتسطر ذخیره‌شده، با مقدارهای خودکار مثل id، برگردانده می‌شودبرگرداندن تغییرخطاها را پنهان می‌کند

3. بعد از BEGIN و چند UPDATE، ROLLBACK چه می‌کند؟

   آخرین UPDATE را برمی‌گرداندهمه‌ی تغییرهای بعد از BEGIN را برمی‌گردانددیتابیس را پاک می‌کندCOMMIT می‌کند

4. خروجی UPDATE 0 یعنی…

   خطاهیچ سطری با WHERE جور نبودهمه عوض شدندتراکنش باز است

5. ON CONFLICT (name) DO NOTHING چه می‌کند؟

   خطا می‌دهداگر name تکراری بود، بی‌صدا رد می‌شودسطر قبلی را پاک می‌کندجدول را قفل می‌کند

6. فرق اصلی TRUNCATE با DELETE؟

   TRUNCATE برگشت‌پذیر نیستTRUNCATE کل جدول را بدون شرط و خیلی سریع خالی می‌کندDELETE سریع‌تر استفرقی ندارند

جمع‌بندی

• INSERT ... VALUES ...، UPDATE ... SET ... WHERE ...، DELETE FROM ... WHERE ...؛ RETURNING نتیجه را همان لحظه نشان می‌دهد.
• همیشه عدد سطرهای عوض‌شده (UPDATE 1) را ببین؛ قبل از DELETE، همان WHERE را با SELECT امتحان کن.
• تراکنش: BEGIN … COMMIT یا ROLLBACK؛ یا همه یا هیچ؛ بعد از خطا فقط ROLLBACK.
• بدون BEGIN، هر دستور فوراً قطعی است (autocommit).
• upsert با ON CONFLICT ... DO UPDATE یا DO NOTHING؛ قیدها برای UPDATE هم اجرا می‌شوند.
• TRUNCATE برای خالی کردن سریع کل جدول.

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
INSERT INTO t (a) VALUES (1) RETURNING id;	افزودن و گرفتن id
UPDATE t SET a = a + 1 WHERE id = 5;	ویرایش با محاسبه
DELETE FROM t WHERE id = 5;	حذف
SELECT ... WHERE (همان شرط)	قبل از DELETE امتحان کن
BEGIN; ... COMMIT;	تراکنش
ROLLBACK;	برگرداندن همه
ON CONFLICT (name) DO UPDATE SET ...	upsert
TRUNCATE t;	خالی کردن سریع