Alter a table in PostgreSQLs
FREE
Overview
Do you know what are steps required to alter the table in Postgresql? How does Postgresql alter the table?
The ALTER TABLE command in PostgreSQL is a crucial SQL statement that allows users to modify the structure of an existing table in a database. This command is widely used for making changes to tables, such as adding or removing columns, altering column data types, renaming columns, and adding or removing constraints.
With ALTER TABLE, you can update your table's structure to meet new requirements without starting from scratch. It's like a toolkit for table improvements over time.
Syntax for ALTER TABLE command in PostgreSQL
The syntax for the ALTER TABLE command in PostgreSQL starts with the keyword ALTER TABLE, followed by the name of the table on which you want to perform the action, and then the specific action you want to execute. Here's the basic syntax:
Now, let's explain each part of the syntax and some common actions that can be used with ALTER TABLE:
- ALTER TABLE: This is the SQL keyword used to indicate that you want to modify an existing table.
- table_name: This is the name of the table you want to alter. Replace table_name with the actual name of your target table.
- action: This is the specific modification or action you want to perform on the table. It can be any of the following:
- Adding a New Column: To add a new column we need to alter the table and add a column in Postgresql by defining its data type also.
- Dropping a Column: To drop a column we need to alter the table and then drop a column by defining its name only.
- Modifying a Column: To modify a column we need to alter the table and then alter the column name by changing its data type.
- Adding a Constraint: To add a new constraint we need to alter the table and then add a constraint by defining the constraint type also.
- Dropping a Constraint: To drop a constraint we need to alter the table and then drop a constraint by defining the constraint name.
- Renaming a Table: To rename a table we need to alter the current table and then rename the table name via the new table name.
- Renaming a Column: To rename a column we need to alter the table and then rename the current column name with the new column name.
In each case, we need to replace table_name with the name of the table we want to alter, column_name with the name of the column we want to add, drop, or modify, and data_type with the new data type (if applicable). Additionally, use constraint_name for the name of the constraint being added or dropped. The constraint_type can be PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK, depending on the type of constraint being added.
Table for ALTER TABLE Commands Modifications
Below table will help to understand the various alter table commands modifications:
| Commands | Description |
|---|---|
| ADD COLUMN | Adds a new column to the table. You need to specify the name of the new column and its data type. New rows will have the NULL value by default until you provide a default value. |
| DROP COLUMN | Removes an existing column from a table. Be aware when using this command, as it permanently deletes data stored in that column. |
| DROP DEFAULT | Removes the default value associated with a column. After using this command, new rows will not receive any default value for that column during insertion. |
| ADD CONSTRAINT | Adds a constraint to a table, such as primary key, foreign key, unique, or check constraints. These Constraints ensure data integrity and enforce rules on the table's data. |
| RENAME COLUMN | Renames an existing column in a table. This command does not affect the data in the column, only its name is changed. |
| RENAME TABLE | Renames an existing table in the database. This operation only changes the table name and does not impact the stored data. |
| ADD CHECK | Adds a check constraint to a table. A check constraint specifies a condition that must be meet for data in a specific column. It ensures that data satisfies the defined condition. |
| NOT NULL CONSTRAINT | Adds a constraint to a column, ensuring that it cannot contain NULL values. Any new or updated rows must have a non-null value for that column. |
ALTER TABLE Command in PostgreSQL Examples
Let’s create a table employees to better understand the commands of the alter table in postgreSQL.
Creating the employees table, using the CREATE TABLE command:
Now, let's insert the data in the above created employees table.
The table has been generated in the below format by inserting the data:
Output:
Let’s perform the alter table commands on the above defined table employees.
ADD COLUMNS
Let's add two new columns email and salary to the employees table:
After executing the above query, below output will be generated:
Output:
DROP COLUMN
Let's drop the age and department columns from the employees table:
After executing the above query, below output will be generated:
Output:
ADD CONSTRAINT
Let's add a UNIQUE constraint on the email column and a CHECK constraint to ensure the salary is greater than or equal to 0:
After executing the above query, below output will be generated:
Output:
RENAME COLUMN
Let's rename the first_name column to given_name:
After executing the above query, below output will be generated:
Output:
RENAME TABLE
Let's rename the employees table to staff:
After executing the above query, below output will be generated:
Output:
ADD CHECK CONSTRAINTS
Let's add a check constraint on the emp_id column to ensure that employees' id is above 0.
After executing the above query, below output will be generated:
Output:
NOT NULL Constraints
Let's add a NOT NULL constraint on the emp_id column to ensure that employees' id is not null.
After executing the above query, below output will be generated:
Output:
Conclusion
- The Alter table ADD COLUMN in the Postgresql clause empowers users to expand the table schema, incorporating new data attributes as business needs evolve.
- Removing columns through the DROP COLUMN clause improves data organisation and reduces unnecessary storage, enhancing overall database efficiency.
- The ALTER COLUMN clause enables the modification of data types, ensuring data representation matches evolving requirements without the need for table recreation.
- PostgreSQL's support for adding constraints with the ADD CONSTRAINT clause ensures data integrity by enforcing rules such as UNIQUE, CHECK, and PRIMARY KEY constraints.
- Removing constraints with the DROP CONSTRAINT clause grants users the flexibility to adjust or lift constraints based on changing data scenarios or application needs.
- The ALTER TABLE commands collectively empower database administrators to efficiently adapt their database schema, optimise performance, and accommodate data modifications while preserving data integrity and coherence.