Prev Next

PostgreSQL ALTER COLUMN

The ALTER TABLE statement in PostgreSQL can also be used to modify existing columns. This is useful when you need to change a column's data type, rename it, or set a new default value.

Key Topics

1. Renaming a Column

ALTER TABLE employees
RENAME COLUMN first_name TO given_name;

Renames the first_name column to given_name.

2. Changing a Column Type

To change a column's data type, you can use the SET DATA TYPE clause. For example:

ALTER TABLE employees
ALTER COLUMN salary SET DATA TYPE DECIMAL(12,2);

Changes the salary column to DECIMAL(12,2).

3. Setting a New Default

ALTER TABLE employees
ALTER COLUMN department SET DEFAULT 'General';

All new rows that don’t specify a value for department will get the default value General.

Best Practices

  • Rename columns carefully to maintain code and query compatibility.
  • Changing data types might require casting existing data. Ensure data compatibility.
  • Use default values to simplify data insertion logic in applications.

Key Takeaways

  • ALTER TABLE provides a range of options for altering column definitions.
  • Renaming, changing data types, and setting defaults are common column modifications.
  • Plan schema changes to minimize disruptions in production environments.
Prev Next