EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Postgres ALTER TABLE
 

Postgres ALTER TABLE

Updated May 25, 2023

Postgres ALTER TABLE

 

 

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 specific columns, 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 usage of the Postgres ALTER TABLE command and view a few examples to understand its application in detail.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Output:

Postgres ALTER TABLE 1

Command:

CREATE TABLE educba
(id INTEGER PRIMARY KEY,
technologies VARCHAR,
workforce INTEGER,
address VARCHAR);

Output:

Postgres ALTER TABLE 2

Check whether the table is created by \dt command that gives output –

\dt

Output:

Postgres ALTER TABLE 3

Example of Postgres ALTER TABLE

Let us add one more column name clientcount of integer datatype with zero as the default value using alter command in the following way –

ALTER TABLE educba ADD COLUMN clientcount INTEGER DEFAULT 0;

Output:

Postgres ALTER TABLE 4

To check our change, we can see the records of the educba table that are as follows –

select * from educba;

Output:

Postgres ALTER TABLE 5

Hence, our column is added successfully.

Let us similarly drop the column address 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;

Output:

Postgres ALTER TABLE 6

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 the below query firing –

select * from educba;

Output:

output 1

For setting the default value to the clientcount column, we can fire the following alter query statement –

ALTER TABLE educba ALTER COLUMN clientcount SET DEFAULT 0 ;

Output:

ALTER TABLE

and verify by inserting a record in the educba table –

insert into educba values (1,'java',50,DEFAULT);

Output:

output 2

select * from educba;

Output:

output 3

We can even add and remove multiple columns simultaneously. Let us insert two columns named departments and starting date of the 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;

output 4

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;

Output:

output 5

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 –

output 6

Now, we will add a new primary key constraint on the id and technologies of the table using the following alter table command –

ALTER TABLE educba ADD CONSTRAINT tech_pkey PRIMARY KEY (id, technologies);

that results in the following output-

output 7

To check the keys, let’s describe the educba table using the command:

\d educba;

Output:

output 8

Hence, our primary key constraint is added successfully.

Format 2

ALTER TABLE [ ONLY ] tableName [ * ]
RENAME [ COLUMN ] existingColumn TO alteredColumn

Now, if we wish to change the name of the column, then we can use the format 2 queries of alter table to fulfill 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 –

Postgres ALTER TABLE 7

Postgres ALTER TABLE 8

Now, to check whether a column is renamed. Let us describe the educba table using \d educba; which results in the following output –

Format 3

ALTER TABLE tableName
RENAME TO alteredTableName

We can even rename the table’s name 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 the \d command after firing the above command to describe the educba and educational_platforms table.

\d educba;

This gives the following output –

Postgres ALTER TABLE 9

Format 4

ALTER TABLE tableName
SET SCHEMA alteredSchema

We can even alter the table schema to another 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, and 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 maximize the utilization of such commands available in PostgreSQL to prepare efficient, robust, and consistent PostgreSQL databases.

Recommended Articles

We hope that this EDUCBA information on “Postgres ALTER TABLE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL EXCEPT
  2. Postgres Delete Cascade
  3. PostgreSQL Roles
  4. PostgreSQL SET

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW