DELETE مع USING وأنماط آمنة

DELETE أساسي

DELETE FROM users WHERE id = 1;

اختبر دائمًا باستخدام SELECT أولًا

SELECT COUNT(*) FROM orders
WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL '1 year';

DELETE داخل معاملة

BEGIN;
DELETE FROM orders WHERE status = 'cancelled' AND ...;
SELECT COUNT(*) FROM orders;       -- sanity check
-- COMMIT;  or ROLLBACK;

DELETE من دون WHERE كارثة

DELETE … USING (حذف بأسلوب JOIN في PostgreSQL)

DELETE FROM orders o
USING users u
WHERE u.id = o.user_id
  AND u.is_banned = true;

DELETE مع استعلام فرعي

DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE is_banned);

تتابعات المفاتيح الخارجية

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;

الحذف المنطقي

UPDATE users SET deleted_at = NOW() WHERE id = $1;

-- Every query then has:
WHERE deleted_at IS NULL

تنظيف البيانات القديمة

DELETE FROM events
WHERE id IN (
  SELECT id FROM events
  WHERE created_at < NOW() - INTERVAL '90 days'
  LIMIT 10000
);
-- run this in a loop until it returns 0 rows

TRUNCATE: عندما تكون السرعة مهمة

TRUNCATE TABLE staging_orders;
TRUNCATE TABLE staging_orders RESTART IDENTITY CASCADE;

استرجاع الصفوف المحذوفة باستخدام RETURNING

WITH deleted AS (
  DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

مراجعة

اختبار سريع