In PostgreSQL, you can use the ALTER TABLE ... ALTER COLUMN statement to modify existing columns in a table. Here are some common use cases:

1. Change Data Type of a Column

ALTER TABLE table_nameALTER COLUMN column_name TYPE new_data_type;

🔹 Example: Change a column age from INTEGER to BIGINT:

ALTER TABLE usersALTER COLUMN age TYPE BIGINT;

⚠️ If the column contains data, ensure that all values are compatible with the new type. You might need to cast values explicitly:

ALTER TABLE usersALTER COLUMN age TYPE BIGINT USING age::BIGINT;

2. Rename a Column

ALTER TABLE table_nameRENAME COLUMN old_column_name TO new_column_name;

🔹 Example:

ALTER TABLE usersRENAME COLUMN fullname TO full_name;

3. Change the Default Value of a Column

ALTER TABLE table_nameALTER COLUMN column_name SET DEFAULT new_default_value;

🔹 Example: Set a default value for status:

ALTER TABLE ordersALTER COLUMN status SET DEFAULT 'pending';

To remove the default value:

ALTER TABLE ordersALTER COLUMN status DROP DEFAULT;

4. Add or Remove a NOT NULL Constraint

🔹 Make a column NOT NULL:

ALTER TABLE usersALTER COLUMN email SET NOT NULL;

🔹 Remove NOT NULL constraint:

ALTER TABLE usersALTER COLUMN email DROP NOT NULL;

⚠️ If there are already NULL values in the column, PostgreSQL will not allow you to add a NOT NULL constraint unless you first update or delete those records.

5. Change Auto-Increment Sequence (SERIAL)

If a column was originally created as SERIAL and you want to ensure it increments automatically:

ALTER SEQUENCE table_name_column_name_seq RESTART WITH 1000;

🔹 Example: Restart the ID sequence at 1000:

ALTER SEQUENCE users_id_seq RESTART WITH 1000;

6. Modify Column Collation (for Text Sorting)

ALTER TABLE usersALTER COLUMN username TYPE VARCHAR(100) COLLATE "C";

This ensures case-sensitive sorting.

7. Increase or Decrease Column Size (VARCHAR)

ALTER TABLE usersALTER COLUMN name TYPE VARCHAR(255);

⚠️ Decreasing size (VARCHAR(100) → VARCHAR(50)) may fail if existing values exceed the new limit.

8. Add a GENERATED Column (Computed Values)

ALTER TABLE usersADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

9. Add or Remove Identity (Auto-increment)

🔹 Make a column an IDENTITY column:

ALTER TABLE usersALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

🔹 Remove identity:

ALTER TABLE usersALTER COLUMN id DROP IDENTITY;

10. Drop a Column

ALTER TABLE usersDROP COLUMN middle_name;