Search⌘ K
Home
Courses
Mastering PostgreSQL Databases: From Basics to Advanced

Advanced Data Definition Commands to Alter Columns

Learn about advanced data definition commands, including commands to add columns, delete columns, and update the constraints of a table.

We'll cover the following...

Adding columns

To add a column to a table, use the ALTER TABLE command with the ADD COLUMN clause.

Here is the syntax for this:

PostgreSQL
ALTER TABLE 
  <table_name> 
ADD COLUMN 
  <column_name> <datatype>;

For example, to add a phone_number column with the datatype VARCHAR(10) to the Customer table, the command would be as follows:

PostgreSQL
\d Customer
SELECT '\n' AS " "; -- Adding new line
ALTER TABLE
  Customer
ADD COLUMN 
  phone_number VARCHAR(10);
\d Customer

  • Lines 1 and 9: The \d command followed by the name of an object (e.g., Customer) will show detailed information about that object.

  • Line 2: This statement returns a new line as a column called " " in the results. The \n character is a new line character used to create a new line in the output. The -- indicates that the remainder of the line is a comment and is ignored by the database.

  • Lines 4–7: The ALTER TABLE command adds a phone_number column with the data type VARCHAR(10) to the Customer table.

Dropping columns

To delete a column, use the ALTER TABLE command with the DROP COLUMN clause.

The syntax for this is given below:

PostgreSQL
ALTER TABLE <table_name> 
  DROP COLUMN <column_name>;

For example, to delete the phone_number column from the Customer table, the command would be as follows:

PostgreSQL
\d Customer
SELECT '\n' AS " "; -- Adding new line
ALTER TABLE Customer 
  DROP COLUMN phone_number;
\d Customer

...