Skip to content
Guide Docs

PostgreSQL Table Operations

This guide covers common table operations in PostgreSQL, including adding columns, modifying table structure, and other essential database management tasks.

Table of Contents

Adding Columns

To add a new column to an existing table, use the ALTER TABLE statement with the ADD COLUMN clause.

Syntax

ALTER TABLE table_name
ADD COLUMN column_name data_type [constraints];

Examples

Add a simple column without constraints:

ALTER TABLE employees
ADD COLUMN email VARCHAR(255);

Add a column with constraints:

ALTER TABLE employees
ADD COLUMN age INTEGER CHECK (age >= 18);

Add a column with a default value:

ALTER TABLE employees
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Add multiple columns at once:

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20),
ADD COLUMN department VARCHAR(100);

Removing Columns

To remove a column from a table, use the ALTER TABLE statement with the DROP COLUMN clause.

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Examples

Remove a single column:

ALTER TABLE employees
DROP COLUMN email;

Remove multiple columns:

ALTER TABLE employees
DROP COLUMN phone,
DROP COLUMN department;

Note: Dropping a column will permanently remove all data in that column.

Modifying Columns

To change the data type or constraints of an existing column, use the ALTER COLUMN clause.

Syntax

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

Examples

Change the data type of a column:

ALTER TABLE employees
ALTER COLUMN age TYPE SMALLINT;

Set a default value for a column:

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

Remove the default value from a column:

ALTER TABLE employees
ALTER COLUMN department DROP DEFAULT;

Set a column to NOT NULL:

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

Remove NOT NULL constraint from a column:

ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

Renaming Columns

To rename a column, use the RENAME COLUMN clause.

Syntax

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Example

ALTER TABLE employees
RENAME COLUMN created_at TO hire_date;

Adding Constraints

You can add constraints to existing columns using the ADD CONSTRAINT clause.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

Examples

Add a primary key constraint:

ALTER TABLE employees
ADD CONSTRAINT pk_employees_id PRIMARY KEY (id);

Add a unique constraint:

ALTER TABLE employees
ADD CONSTRAINT uk_employees_email UNIQUE (email);

Add a foreign key constraint:

ALTER TABLE employees
ADD CONSTRAINT fk_employees_department
FOREIGN KEY (department_id) REFERENCES departments(id);

Add a check constraint:

ALTER TABLE employees
ADD CONSTRAINT chk_employees_age CHECK (age >= 18);

Removing Constraints

To remove constraints, use the DROP CONSTRAINT clause.

Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Examples

Remove a named constraint:

ALTER TABLE employees
DROP CONSTRAINT uk_employees_email;

Remove a primary key constraint:

ALTER TABLE employees
DROP CONSTRAINT pk_employees_id;

Note: If you don’t know the constraint name, you can find it by querying the information schema or using pg_constraint system table.

Renaming Tables

To rename a table, use the RENAME TO clause.

Syntax

ALTER TABLE old_table_name
RENAME TO new_table_name;

Example

ALTER TABLE employees
RENAME TO staff;

Truncating Tables

To remove all rows from a table while keeping the table structure, use the TRUNCATE statement.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE employees;

Note: TRUNCATE is faster than DELETE without a WHERE clause, but it cannot be rolled back in some database systems and does not trigger ON DELETE triggers.

Best Practices

  1. Always backup your database before making structural changes
  2. Test ALTER TABLE operations on a development database first
  3. Consider the impact on indexes, constraints, and foreign keys when modifying tables
  4. Use transactions for critical table modifications when possible
  5. Document all structural changes to your database schema