SQL ALTER TABLE, DROP and TRUNCATE

ALTER TABLE changes an existing table's structure, DROP removes a table entirely, and TRUNCATE empties a table but keeps it. Business needs change, and tables change with them: a new column for loyalty points, a renamed field, a tighter rule.

ALTER TABLE

Syntax

ALTER TABLE table_name ADD COLUMN column_name data_type;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_type;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name ADD CONSTRAINT name CHECK (...);
ALTER TABLE old_name RENAME TO new_name;

1. Add a column

ShopKart launches a loyalty programme:

ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;

SELECT customer_name, loyalty_points FROM customers LIMIT 3;

Result:

(3 rows)

Existing rows get the default.

2. Fill the new column

UPDATE customers c
SET loyalty_points = sub.points
FROM (
  SELECT o.customer_id, FLOOR(SUM(oi.quantity * oi.unit_price) / 100) AS points
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY o.customer_id
) sub
WHERE c.customer_id = sub.customer_id;

SELECT customer_name, loyalty_points
FROM customers
ORDER BY loyalty_points DESC
LIMIT 3;

Result:

(3 rows)

3. Rename a column

ALTER TABLE customers RENAME COLUMN loyalty_points TO reward_points;

SELECT customer_name, reward_points FROM customers LIMIT 2;

Result:

(2 rows)

Renaming breaks any saved query, view or report that used the old name, so check dependencies first.

4. Change a column's type

Allow longer product names:

ALTER TABLE products ALTER COLUMN product_name TYPE VARCHAR(200);

SELECT character_maximum_length
FROM information_schema.columns
WHERE table_name = 'products' AND column_name = 'product_name';

Result:

(1 row)

5. Add a constraint to an existing table

ALTER TABLE products
ADD CONSTRAINT chk_price_above_cost CHECK (price >= cost_price);

Result:

Query OK, 0 row(s) affected.

The database checks existing rows first. All ShopKart prices are above cost, so it succeeds. Now a loss-making price is blocked:

UPDATE products SET price = 100 WHERE product_id = 101;

Error: new row for relation "products" violates check constraint "chk_price_above_cost"

6. Drop a column

ALTER TABLE customers DROP COLUMN reward_points;

SELECT COUNT(*) AS columns_now
FROM information_schema.columns
WHERE table_name = 'customers';

Result:

(1 row)

DROP, TRUNCATE and DELETE

Set up a scratch table to compare them:

CREATE TABLE order_archive AS SELECT * FROM orders;
SELECT COUNT(*) AS rows FROM order_archive;

Result:

(1 row)

DELETE: remove chosen rows

DELETE FROM order_archive WHERE order_date < '2025-07-01';
SELECT COUNT(*) AS rows_left FROM order_archive;

Result:

(1 row)

TRUNCATE: remove all rows, keep the table

TRUNCATE TABLE order_archive;
SELECT COUNT(*) AS rows_left FROM order_archive;

Result:

(1 row)

DROP: remove the table itself

DROP TABLE order_archive;

Result:

Query OK, 0 row(s) affected.

SELECT * FROM order_archive;

Error: relation "order_archive" does not exist

DROP TABLE IF EXISTS order_archive; avoids an error when the table might not exist.

Common mistakes

• DROP in the wrong database. There's no undo outside a transaction or a backup. Double-check the connection before running it.
• Renaming columns used by dashboards. Reports break silently. Add a new column and migrate gradually instead.
• Using TRUNCATE on a table referenced by foreign keys. PostgreSQL refuses unless you add CASCADE, which also empties the referencing tables.

Practice question

Add a return_window_days column to categories with a default of 7, then set it to 10 for Fashion and show the table.

ALTER TABLE categories ADD COLUMN return_window_days INT DEFAULT 7;

UPDATE categories SET return_window_days = 10 WHERE category_name = 'Fashion';

SELECT * FROM categories ORDER BY category_id;

Key takeaways

• ALTER TABLE adds, renames, retypes and drops columns, and adds constraints.
• DELETE removes chosen rows, TRUNCATE empties a table, DROP deletes it.
• Structural changes can break queries and reports, so check dependencies.
• Use IF EXISTS / IF NOT EXISTS to make scripts safe to re-run.