How to Alter Table in PostgreSQL Database by Adding a New Column

This article primarily contains information about how to alter table in PostgreSQL Database. In order to alter table in PostgreSQL Database, the following are the steps to achieve it :

1. Access the PostgreSQL Database Server. In this article, the access is using a CLI (Command Line Interface) mode. Before continue on further, just access the PostgreSQL CLI first :

user@hostname:~$ psql -Upostgres 
psql (10.5)
Type "help" for help.
postgres=>

2. After accessing the PostgreSQL CLI, connect to the suitable database first :

postgres=> \c dbname
You are now connected to database "dbname" as user "postgres". 
dbname=> 

3. After connecting to the correct dastabase, just list the table available by typing the following command :

alter table tablename add column status boolean;

Just type the above command in a real situation for executing the query :

dbname=# \dt
                 List of relations
 Schema |           Name           | Type  | Owner 
--------+--------------------------+-------+-------
 public | table_0                  | table | postgres
 public | table_1                  | table | postgres
 public | table_2                  | table | postgres
...
(xx rows)
dbname=#

4. Don’t forget to describe the table first by typing the following query in the PostgreSQL CLI :

dbname=# \d+ table_0;
                                                          Table "public.app"
 Column |          Type          | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer                |           | not null | nextval('app_app_id_seq'::regclass) | plain    |              | 
 name   | character varying(255) |           |          |                                     | extended |              | 
Indexes:
    "app_pkey" PRIMARY KEY, btree (app_id)
dbname=# 

5. The output of the above query execution is just an example. For an example, if the table with the name of table_0 is going for the alteration process then do the following step. Just execute this query below :

dbname=# alter table table_01 add column status boolean;
ALTER TABLE
dbname=#

7. The next step, after altering the table, just re-check the table. Do it by typing the exact query in the fourth step. The following is the output of the query execution :

dbname=# \d+ table_0;
                                                          Table "public.app"
 Column |          Type          | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer                |           | not null | nextval('app_app_id_seq'::regclass) | plain    |              | 
 name   | character varying(255) |           |          |                                     | extended |              | 
 status | boolean                |           |          |                                     | plain    |              | 
Indexes:
    "app_pkey" PRIMARY KEY, btree (id)
dbname=# 

As the above output shows, there is an additional column in the table with the name of table_0. The new column’s name is ‘status’.