ALTER, DROP & TRUNCATE

ALTER TABLE

ALTER TABLE students ADD COLUMN phone varchar(20);
ALTER TABLE students DROP COLUMN phone;
ALTER TABLE students RENAME COLUMN cgpa TO gpa;
ALTER TABLE students RENAME TO learners;

ALTER TABLE students ALTER COLUMN name_bn SET NOT NULL;
ALTER TABLE students ALTER COLUMN name_bn DROP NOT NULL;
ALTER TABLE students ALTER COLUMN department SET DEFAULT 'EEE';
ALTER TABLE students ALTER COLUMN department DROP DEFAULT;

-- type change, with an explicit conversion when needed
ALTER TABLE students ALTER COLUMN age TYPE integer;
ALTER TABLE students ALTER COLUMN roll_no TYPE text USING roll_no::text;

ALTER TABLE students ADD CONSTRAINT chk_cgpa CHECK (cgpa <= 4.0);
ALTER TABLE students DROP CONSTRAINT chk_cgpa;

ALTER TABLE students SET SCHEMA academic;

Several actions can be combined, which takes one lock instead of several:

ALTER TABLE students
  ADD COLUMN city text,
  ALTER COLUMN age TYPE smallint,
  ADD CONSTRAINT chk_age CHECK (age > 0);

DDL is transactional

Unlike MySQL, PostgreSQL DDL runs inside transactions. This is a genuinely important feature — a failed migration rolls all the way back:

BEGIN;
  ALTER TABLE students ADD COLUMN city text;
  UPDATE students SET city = 'Chattogram';
  -- something goes wrong
ROLLBACK;   -- the column never existed

Locking and safe migrations

ALTER TABLE usually takes an ACCESS EXCLUSIVE lock, which blocks every reader and writer for the duration. These operations are fast (metadata-only) in modern Postgres:

• ADD COLUMN with a constant or no default (11+)
• DROP COLUMN
• Renaming anything
• SET NOT NULL when a validated CHECK (col IS NOT NULL) already exists (12+)

These rewrite the whole table and should be done carefully on large tables: most type changes, and adding a volatile default.

Two safety habits for production:

SET lock_timeout = '3s';     -- fail fast instead of queuing behind a long query

-- add a constraint without a long lock: validate separately
ALTER TABLE students ADD CONSTRAINT chk_age CHECK (age > 0) NOT VALID;
ALTER TABLE students VALIDATE CONSTRAINT chk_age;   -- takes only a weak lock

DELETE vs TRUNCATE vs DROP

	DELETE	TRUNCATE	DROP
Removes	Selected rows	All rows	Rows and the table
WHERE	Yes	No	No
Transactional / rollback	Yes	Yes (unlike MySQL)	Yes
Fires row triggers	Yes	No (only statement triggers)	No
Reclaims space	Needs VACUUM	Immediately	Immediately
Speed	Slow on big tables	Very fast	Very fast

DELETE FROM students WHERE cgpa < 2.0;
TRUNCATE TABLE students RESTART IDENTITY CASCADE;
DROP TABLE IF EXISTS students CASCADE;

RESTART IDENTITY resets the identity/serial counter; CASCADE also truncates tables that reference this one.

Dependencies

DROP TABLE departments;            -- fails if a view or FK depends on it
DROP TABLE departments CASCADE;    -- drops the dependent objects too

Run the plain form first and read the error — it tells you exactly what depends on the object, which is usually information you want before reaching for CASCADE.