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:

psql

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

postgres=# CREATE DATABASE testdb;

Creating a table in a PostgreSQL database

  • The syntax for the psql SQL command to create table is as follows:
postgres=# CREATE TABLE [TABLE_NAME] (COLUMN name + DATA TYPE + constraints IF any);

For example:

postgres=# CREATE TABLE employee(id INT NOT NULL PRIMARY KEY, emp_name text, emp_address CHAR(50), emp_contact INT NOT NULL, salary REAL);

Granting a user privileges to a PostgreSQL table

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

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO someUser
  • 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:

\c 'database_name'

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:

ALTER TABLE TABLE_NAME ADD new_column_name VARCHAR (20);

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:

\d+ some_table

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

 new_column_name | CHARACTER VARYING(20)

Screenshot of psql returning all of the columns in a PostgreSQL table

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

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

postgres=# ALTER TABLE employee ADD join_date TIMESTAMP;

Adding multiple columns to a PostgreSQL table

Dropping a column from a PostgreSQL table

ALTER TABLE TABLE_NAME ALTER COLUMN column_name TYPE column_definition;
postgres=# ALTER TABLE employee ALTER COLUMN join_date TYPE DATE;

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

ALTER TABLE TABLE_NAME DROP COLUMN column_name;
postgres=# ALTER TABLE employee DROP COLUMN emp_contact;

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

ALTER TABLE TABLE_NAME RENAME COLUMN old_name TO new_name;
postgres=# ALTER TABLE employee RENAME emp_name TO emp_fullname;

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

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.