How to Add Column of a Table in PostgreSQL Database

By Posted on

Add Column of a Table in PostgreSQL Database

This article’s main content is about adding a column in a specific table in PostgreSQL database. So, there is a table exist in a database. So, there will be a possibility for adding a column in that table. The following steps below is showing how to add a column in a table in the PostgreSQL database :

  1. First of all, just try to login to the PostgreSQL command console as follows : 

    [root@host ~]# psql -Upostgres -d db_apps
psql (11.10)
Type "help" for help.

db_apps=#

  2. Next, following after the successful login process, just connect to the database where the table available. In this context, it is where the additional column will be added to the table. Just execute the following command : 

    db_apps=# \c db_apps;
You are now connected to database "db_apps" as user "postgres".
db_apps=#

    The above step is just to make sure that currently the database for adding the column is the right one.

  3. Apparently, in order to continue on the step, just check the available tables in the database : 

    db_apps=# employee=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | employee | table | postgres
(1 row)


db_apps=#

  4. Fortunately, there is only one table. That table has the name of ’employee’. So, following after, describe the table to list the available column by executing the following query : 

    db_apps=# \d+ employee
                                                          Table "public.employee"
   Column    |         Type          | Collation | Nullable |               Default                | Storage  | Stats target | Description
-------------+-----------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer               |           | not null | nextval('employee_id_seq'::regclass) | plain    |              |
 employee_id | character varying(18) |           | not null |                                      | extended |              |
 name        | character varying     |           | not null |                                      | extended |              |
Access method: heap


db_apps=#

  5. After knowing the structure of the table, just execute the command for adding a new column. In this context, for an example it is a table with an additional column with the name of ’email’. It is a column to store the email value of the employee. So, the query for altering the table to add the column exist as follows : 

    db_apps=# alter table employee add email varchar(50);
ALTER TABLE
db_apps=#

    Another version from the above query where is the full version of it exist as follows : 

    db_apps=# ALTER TABLE "public"."employee" ADD COLUMN email character varying(50) COLLATE pg_catalog."default";
ALTER TABLE
db_apps=#

  6. Finally, in order to check that the new added column is available, just execute the same query for describing the table as follows : 

    db_apps=# \d+ employee
                                                          Table "public.employee"
   Column    |         Type          | Collation | Nullable |               Default                | Storage  | Stats target | Description
-------------+-----------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer               |           | not null | nextval('employee_id_seq'::regclass) | plain    |              |
 employee_id | character varying(18) |           | not null |                                      | extended |              |
 name        | character varying     |           | not null |                                      | extended |              |
 email       | character varying(50) |           |          |                                      | extended |              |
Access method: heap


db_apps=#

Leave a Reply