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

By Posted on

Introduction on the altering table process in PostgreSQL Database

This is another article consist of a specific subject about PostgreSQL Database server. It is an administration task for adding a new column with a default value in PostgreSQL Database Server. Another article with a similar content exist in the following link . The article has a title of ‘How to Alter Table in PostgreSQL Database by Adding a New Column’. But in this article, there is something different. The process for adding a new column has another specific detail. The new column must have a default value. This value will exist by default when a new record appear. Specifically if that new record with the associated column does not have any value. By default, the value will be the one exist in the column definition from the table alteration process.

Steps for altering table process in PostgreSQL Database

Without any further explanation, below are the steps for achieving the purpose :

1. As usual, access the PostgreSQL Database Server. In this article, the access is a success if the PostgreSQL Command Line Interface appear. The following is an example for accessing the PostgreSQL Command Line Interface : 

user@hostname:~$ psql -Uuser dbname
psql (10.5)
Type "help" for help.
dbname=#

2. Connect to a specific database. Just execute the following command : 

dbname=#\c mydbname;
You are now connected to database "mydbname" as user "user".
dbname=#

3. Before altering the database, just check the table first by listing all the available tables exist in the database : 

mydbname=# \dt;
                 List of relations
 Schema |           Name           | Type  | Owner 
--------+--------------------------+-------+-------
 public | table_0                  | table | postgres
 public | table_1                  | table | postgres
(XX rows)
mydbname=#

4. After that, show the structure of the table which is up for further alteration process by executing the following command : 

mydbname=# \d+ app;
                                                          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 (id)
mydbname=#

5. execute the following query to alter the table : 

mydbname=# alter table app add status boolean default 0::boolean;
ALTER TABLE
mydbname=# ;

6. Last but not least, recheck the table structure after executing the above query : 

mydbname=# alter table app add status boolean default 0::boolean;
ALTER TABLE
mydbname=# \d+ app;
                                                          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                |           |          | (0)::boolean                        | plain    |              | 
Indexes:
    "app_pkey" PRIMARY KEY, btree (app_id)
mydbname=#

7. As in the output of the above query execution, there is an additional step to test the new column after the alteration process. Try insert a new record as follows : 

mydbname=# insert into tablename(name) values('test');
INSERT 0 1
mydbname=#

8. Check if the column with the name status has a default value of 0. It is because there is no value specified in the insert query above. But eventhough there is no value defined, since it has been defined with a default value of 0, there is a specific value for the column with the name of status. Type the following query : 

mydbname=# select * from tablename;
     id |                         name                         | status 
--------+------------------------------------------------------+--------
    ...
     68 | test                                                 | f
(X rows)
mydbname=#

As it shows in the above output, the column with the name ‘status’ has a value of ‘f’. It stands for ‘false’ in the boolean type term. It is actually equals 0.

Leave a Reply