Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Advanced Data Definition Commands to Alter Columns

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:

Press + to interact
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:

Press + to interact
\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:

Press + to interact
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:

Press + to interact
\d Customer
SELECT '\n' AS " "; -- Adding new line
ALTER TABLE Customer 
  DROP COLUMN phone_number;
\d Customer

Lines ...