Alter The PostgreSQL Table Columns

Introduction to PostgreSQL Alter Table.

WRITERS: Please write introduction on altering the columns of a pre-existing PostgreSQL table.

All of the PostgreSQL statements for tables

The following is a list of SQL statements that you can use to modify a PostgreSQL table:

• ADD – using the alter table add to create additional column in a table.
• MODIFY – using the alter table alter to revise the column, and the data type.
• DROP – using the alter table drop to delete the column from the table.
• RENAME – using the alter table rename to change the column name of the given table.

This article will cover the ALTER TABLE statement.

Create postgreSQL database

By using the command prompt (on windows), or terminal on (mac and linux), you can create a database in PostgreSQL. First, enter into the psql command-line interface:

Once inside of the psql interface use the following SQL statement to create the database:

Creating a table in a PostgreSQL database

• The syntax for the psql SQL command to create table is as follows:

For example:

Granting a user privileges to a PostgreSQL table

Use the following SQL statement to grant privilege to create a PostgreSQL table using a database:

• Using the command \l will show you the list of all the databases that are already in postgreSQL.

• Connecting to the database using the command:

In here, we are gonna use the database that we have created earlier: \c testdb;

NOTE: Use the only database that you have created.

Now that we have created a table, let’s proceed to the altering process.

Using the ‘ALTER TABLE’ command to add a column to the PostgreSQL table

The following is an example of using the ALTER TABLE SQL command to add a VARCHAR column to the table:

NOTE That you can also insert multiple columns in the table that you want.

Return a list of the PostgreSQL table’s columns

Now we can use the \d+ command to check to make sure that the ALTER TABLE command worked properly by having it return all of the column names:

The above code should return a table response including the new column data type that looks something like this:

Adding a ‘TIMESTAMP’ column to pre-existing PostgreSQL table

Here’s how to add a timestamp column to pre-existing PostgreSQL table:

Adding multiple columns to a PostgreSQL table

Dropping a column from a PostgreSQL table

NOTE That you can also modify multiple columns in a table.

Drop multiple columns in the table using the syntax `ALTER TABLE table_name DROP COLUMN column_name1, column_name2;

** You can also rename your table to a new one using the “ALTER TABLE table_name RENAME TO new_table_name;