PostgreSQL allows you to modify database objects such as tables, databases, schemas, group, users, and more. In this tutorial, we will focus on how you can ALTER the structure of a table.
Basic Usage
The PostgreSQL ALTER query is pretty straightforward. It follows the simple syntax below:
ALTER TABLE table_name TASK;
In this case, the action includes the supported PostgreSQL operations. These include:
- ADD or DELETE a column
- MODIFY column data type
- RENAME a table
- RENAME a column
- ADD column constraint
- SET or DROP column DEFAULT value.
- DROP IDENTITY
- ADD table constraint
- ALTER CONSTRAINT
- ATTACHING PARTITIONS
- SET schema
And many more. Learn more about ALTER TABLE operations in the documentation.
Below are example operations you can perform on a table using the ALTER TABLE query.
Add or Drop Column
One of the most common use cases of the ALTER TABLE command is to add a new column to the table.
The syntax is as:
ALTER TABLE table_name ADD COLUMN column_name data_type constraint;
The query above will append the column with the specified column name, data type, and constraints to the table.
To drop a column, you can use the DROP COLUMN query as:
ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;
Modify Column Data type
Another use of the ALTER TABLE command is to change a column data type. An example query for that is:
ALTER TABLE table_name ALTER COLUMN column_name TYPE VARCHAR(255);
In the above example, we use the ALTER commands (TABLE & COLUMN) to change the data type to a VARCHAR.
Rename a Table
We can also use the ALTER TABLE query to rename a table. The syntax for that is:
ALTER TABLE old_table_name RENAME TO new_table_name;
Rename a Column
We can also use ALTER to rename a column by expressing the query as:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
Add/Remove Column Default Value
To add or remove a column constraint, we can use the query:
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT “value”;
To drop a default value:
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
Add Table Constraint
To add a table constraint using the ALTER TABLE command, we can do:
ALTER TABLE table_name ADD CONSTRAINT constraint_name, constraint_def;
In the above example, we specify the column name and column definition after the ADD constraint query.
Alter Constraint
To alter an existing table constraint, we can use the query:
ALTER TABLE table_name ALTER CONSTRAINT constraint_name;
Add Check Condition
To set a column that accepts only specific values, we can set a check condition with target values.
Here is an example query:
ALTER TABLE table_name ADD CHECK (column_name IN (check_var1, check_var2, check_var3, check_varn));
The above condition ensures that the specified column only contains the specified values.
Set Table Schema
To change the table schema, use the query:
ALTER TABLE current_schema.table_name SET SCHEMA another_schema;
Remove Not Null Constraint
You can also remove a not null constraint from a column using the query:
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;
Rename Existing Constraint
You can also use the ALTER TABLE to rename an existing constraint. The general syntax for that is:
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name.
Closing
The above examples show how you can use the ALTER TABLE command to change a table’s structure. Although we have provided the syntax for the most common operations, there are more actions you can do.
I would recommend checking the official documentation for other actions.
About the author
<img alt="" data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/john-150×150.png611b081403d81.jpg" height="112" src="data:image/svg xml,” width=”112″>
John Otieno
My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list
