Introduction to Postgres ALTER TABLE
Whenever we perform manipulations on the table in the postgreSQL database, we often feel the necessity to perform some changes on the existing table. We can add more columns to the table, modify the existing column and its related properties, add or remove the constraints, remove columns, rename the table or assign the default value to certain columns of the table, add or remove primary, and foreign key constraints and many more. We can perform all these operations using the ALTER TABLE command in postgreSQL. In this article, we will study the syntax and general usages of the ALTER TABLE command and view a few examples to understand its application in detail.
Syntax:
Format 1 –
ALTER TABLE [ ONLY ] tableName [ * ] actionOnTable [, ... ]
- tableName – Name of the table which you want to alter or modify.
- ActionOnTable – It can be any of the following actions you wish to perform on the target table –
add, drop and modify columns and their datatype, add or remove the constraints and rules and properties like replicas, primary and foreign constraints, enabling and disabling the triggers on the table, set OIDS and clusters, inheriting and not inheriting the parent properties, and resettable properties. The constraint which can be added should be mentioned in the following way –
[ CONSTRAINT nameOfConstraint ]
{ UNIQUE | PRIMARY KEY } USING INDEX nameOfIndex
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Let us begin by creating a new table named educba which will store the data related to the corporation after checking all the present tables in our current db by using \dt command –
\dt
CREATE TABLE educba
(id INTEGER PRIMARY KEY,
technologies VARCHAR,
workforce INTEGER,
address VARCHAR);
Check whether the table is created by \dt command that gives output –
\dt
Example of Postgres ALTER TABLE
Let us add one more column name clientcount of integer datatype with zero as default value using alter command in the following way –
ALTER TABLE educba ADD COLUMN clientcount INTEGER DEFAULT 0;
To check our change, we can see the records of educba table that are as follows –
select * from educba;
Hence, our column is added successfully.
Let us drop the column address in a similar way as we added.
ALTER TABLE educba DROP COLUMN address;
and check by selecting the records from the educba whether our column is dropped. Here is the output we get –
select * from educba;
Now, if we want to change the name of the column named workforce to team count, we can do so by firing the following query –
ALTER TABLE educba RENAME COLUMN workforce TO teamcount;
that gives the following output after below query firing –
select * from educba;
For setting the default value to clientcount column we can fire the following alter query statement –
ALTER TABLE educba ALTER COLUMN clientcount SET DEFAULT 0 ;
and verify by inserting a record in educba table –
insert into educba values (1,'java',50,DEFAULT);
select * from educba;
We can even add and remove multiple columns simultaneously. Let us insert two columns named departments and starting date of varchar datatype. We can even perform column related alter queries without using the keyword COLUMN in it. For example –
ALTER TABLE educba
ADD departments varchar,
ADD startingdate varchar;
and check our change using the below query that gives us the following output –
SELECT * FROM educba;
To describe the table in psql, you can fire the command \d and the name of a table as the parameter in the following way –
Syntax:
\d nameOfTable
For describing the educba table, we can use the below command:
\d educba;
Now, to drop the primary constraint educba_pkey, we can use the alter command in the following way –
ALTER TABLE educba DROP CONSTRAINT educba_pkey;
\d educba;
firing which gives the output as follows –
Now, we will add a new primary key constraint on the tables id and technologies using the following alter table command –
ALTER TABLE educba ADD CONSTRAINT tech_pkey PRIMARY KEY (id, technologies);
that results in the following output-
To check the keys lets describe the educba table using the command:
\d educba;
Hence, our primary key constraint is added successfully.
Format 2
ALTER TABLE [ ONLY ] tableName [ * ]
RENAME [ COLUMN ] existingColumn TO alteredColumn
Now, in case if we wish to change the name of the column then we can use the format 2 queries of alter table for fulfilling our purpose.\
Suppose, we want to change the name of the column technologies to technology. Then we can fire the following query to do so –
ALTER TABLE educba RENAME technologies TO technology;
\d educba;
Above query results in the following output –
Now, to check whether a column is renamed. Let us describe the educba table using \d educba; that results in the following output –
Format 3
ALTER TABLE tableName
RENAME TO alteredTableName
We can even rename the name of the table using the alter table command. To rename the name of the table educba to educational_platforms, we can use format 3 of alter table command in the following way –
ALTER TABLE educba RENAME TO educational_platforms;
Let us check using \d command after firing the above command to describe educba and educational_platforms table.
\d educba;
This gives the following output –
Format 4
ALTER TABLE tableName
SET SCHEMA alteredSchema
We can even alter the schema of the table to some other schema using the above format.
Conclusion
We have a versatile and variable ALTER TABLE command in the PostgreSQL database that can be used to add, remove, and modify the columns their datatypes, constraints such as not null, default, etc. Other than this the replica, trigger enabling, and disabling can be done using the same alter table command. The primary key and foreign key constraints can be added and removed and indexes can be modified using the same command. We should try to achieve maximum utilization of such commands that are available in PostgreSQL to prepare efficient, robust, and consistent PostgreSQL databases.
Recommended Articles
This is a guide to Postgres ALTER TABLE. Here we discuss introduction, syntax, format, and examples with code implementation. You may also have a look at the following articles to learn more –
