72 / 100

DROP COLUMN

Ustunni o'chirib tashlash

Kirish: Ushbu qo'llanmada siz PostgreSQL ALTER TABLE ... DROP COLUMN buyrug'i yordamida jadvaldan ortiqcha yoki eskirgan ustunlarni butunlay o'chirib tashlashni o'rganasiz.

ALTER TABLE DROP COLUMN buyrug'iga kirish

Ustun o'z ahamiyatini yo'qotganda xotirani tejash va unumdorlikni saqlab qolish uchun uni jadvaldan olib tashlash kerak bo'ladi. Buning uchun ALTER TABLE ... DROP COLUMN buyrug'i qo'llaniladi:

query.sql

ALTER TABLE table_name
DROP COLUMN column_name;

Xatolikning oldini olish uchun IF EXISTS opsiyasini qo'shish tavsiya etiladi:

query.sql

ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name;

Ustun o'chirilganda, PostgreSQL undagi barcha ma'lumotlarni, shuningdek, ushbu ustunga bog'langan barcha cheklovlar (CHECK, UNIQUE) va indekslarni avtomatik tarzda o'chiradi.

Agar ustun jadvaldan tashqaridagi boshqa obyektlar (masalan, VIEW yoki FOREIGN KEY) tomonidan ishlatilayotgan bo'lsa, PostgreSQL xatolik chiqaradi. Bunday holda bog'liq obyektlar bilan birgalikda o'chirish uchun CASCADE kalit so'zidan foydalaniladi:

query.sql

ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name CASCADE;

Bir vaqtning o'zida bir nechta ustunni o'chirish:

query.sql

ALTER TABLE table_name
DROP COLUMN column1,
DROP COLUMN column2;

Bitta ustunni o'chirishga misol

Yetkazib beruvchilar (suppliers) jadvalini yaratamiz:

query.sql

CREATE TABLE suppliers (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   phone VARCHAR(25) NOT NULL UNIQUE,
   email VARCHAR(255) NOT NULL UNIQUE,
   fax VARCHAR(25) NOT NULL UNIQUE,
   address TEXT,
   notes TEXT
);

Jadvaldan notes ustunini o'chirib tashlaymiz:

query.sql

ALTER TABLE suppliers 
DROP COLUMN notes;

Bog'liq obyektlarga ega ustunni CASCADE bilan o'chirish

suppliers jadvalidagi fax ustuniga bog'langan boshqa jadval va ko'rinish yaratamiz:

query.sql

CREATE TABLE fax_messages (
     id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     fax VARCHAR(255) NOT NULL,
     message TEXT NOT NULL,
     sent_at TIMESTAMPTZ NOT NULL,
     FOREIGN KEY (fax) REFERENCES suppliers(fax) ON DELETE CASCADE
);

CREATE VIEW faxes AS
SELECT name, fax
FROM suppliers;

Agar endi oddiygina DROP COLUMN fax qilsak:

matn

ERROR: cannot drop column fax of table suppliers because other objects depend on it
DETAIL: constraint fax_messages_fax_fkey on table fax_messages depends on column fax of table suppliers
view faxes depends on column fax of table suppliers
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Barcha bog'liqliklar bilan birga o'chirish uchun CASCADE dan foydalanamiz:

query.sql

ALTER TABLE suppliers 
DROP COLUMN fax CASCADE;

PostgreSQL xabar beradi:

matn

NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to constraint fax_messages_fax_fkey on table fax_messages
drop cascades to view faxes

fax ustuni, faxes ko'rinishi va unga bog'langan tashqi kalit cheklovi xavfsiz tarzda o'chirildi.

Bir vaqtda bir nechta ustunni o'chirish

phone va email ustunlarini bitta buyruqda o'chiramiz:

query.sql

ALTER TABLE suppliers
DROP COLUMN phone,
DROP COLUMN email;

Xulosa

Ustunni o'chirish uchun ALTER TABLE table_name DROP COLUMN column_name; dan foydalaning.
Ustun boshqa tashqi kalitlar yoki ko'rinishlarda ishlatilayotgan bo'lsa, CASCADE qo'shimchasidan foydalaning.
Ustun topilmasa xatolik chiqmasligi uchun IF EXISTS ni qo'shing.

Oldingi

Keyingi