Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL ALTER COLUMN

PostgreSQL Tables & Schema Management involves commands and functionalities for defining, modifying, and maintaining database structures and schemas. It is crucial for organizing data efficiently and ensuring database integrity and performance.

Using Tables & Schema Management

Tables & Schema Management is used to create, alter, and delete tables and schemas, enabling the structure of a database to evolve as requirements change. It allows for modifications such as adding columns, changing data types, and setting constraints.

ALTER TABLE table_name
ALTER COLUMN column_name [TYPE data_type | SET DATA TYPE data_type | SET DEFAULT default_value | DROP DEFAULT | SET NOT NULL | DROP NOT NULL];

In this syntax, ALTER TABLE modifies the specified table, and ALTER COLUMN is used to change the attributes of a given column. Note that SET DATA TYPE is equivalent to just TYPE.

Examples

1. Changing Data Type

ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC;

This example changes the data type of the salary column in the employees table to NUMERIC, accommodating decimal values. Be mindful of potential data loss when changing data types.

2. Adding a Default Value

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

Here, a default value of 'pending' is set for the status column in the orders table, ensuring a default entry if no value is provided during insertion.

3. Removing a NOT NULL Constraint

ALTER TABLE products
ALTER COLUMN description DROP NOT NULL;

This command removes the NOT NULL constraint from the description column in the products table, allowing it to accept null values.

4. Adding a New Column with Constraints

ALTER TABLE customers
ADD COLUMN email VARCHAR(255) NOT NULL;

This adds a new email column to the customers table with a NOT NULL constraint.

5. Dropping a Column

ALTER TABLE orders
DROP COLUMN obsolete_column;

This command removes the obsolete_column from the orders table.

Tips and Best Practices

  • Backup before modification. Always backup your database before performing structural changes to prevent data loss.
  • Test changes in a development environment. Before applying changes to a production database, test them in a development environment to identify potential issues.
  • Use transactions for safety. Wrap schema changes in a transaction block to ensure atomicity and rollback capability in case of an error.
  • Consider database locks. Be aware that altering tables can lock them, potentially impacting performance, so schedule changes during low-usage times.
  • Document schema changes. Maintain documentation of all schema changes for future reference and audits.
  • Version control for schema changes. Implement version control to track and manage schema changes systematically.
  • Utilize database migration tools. Consider using tools like Flyway or Liquibase to manage schema changes more systematically and efficiently.

In addition to tables, managing schemas themselves is an essential part of database management. This includes creating, dropping, and altering schemas to organize and isolate database objects effectively.