How to Rename Column in a Table in PostgreSQL Database Server

Posted on

The focus on the article is merely just to change the title or the name of the column as it can be specified in the title of this article. In order to be able to change or to rename the name of the column, below are the steps enlisted in order to achieve or to accomplish the task :

1. Login to the PostgreSQL Database Server.

By logging in to the PostgreSQL Database Server, in this case, it is done by executing the login command via command line, there will be a chance in order to be able to execute the command for changing the name column. Below is normally the login process looks like :

root@hostname:~# psql -Uusername databasename
psql (9.6.9, server 10.5)
WARNING: psql major version 9.6, server major version 10.
Some psql features might not work.
Type "help" for help.

databasename=#

2. After successfully logging in to the PostgreSQL command console, below is the first step in order to change the column’s name. Just list the table existed in the table by executing the following command :

databasename=# \dt

For an example :

databasename=# \dt
                 List of relations
 Schema |           Name           | Type  | Owner 
--------+--------------------------+-------+-------
 ...
 public | users                    | table | pgsql
(17 rows)

databasename=# 

3. As it can be, there is a table named ‘users’ as shown in the above output. Continuing on the process, just list the available column exist in the table. The command itself is shown below¬† :

databasename=# \d+ users                                                                                                                                                                                                                 
                                                        Table "public.users"                                                                                                                                                      
  Column  |          Type          |                        Modifiers                        | Storage  | Stats target | Description                                                                                              
----------+------------------------+---------------------------------------------------------+----------+--------------+-------------                                                                                             
 user_id  | integer                | not null default nextval('users_user_id_seq'::regclass) | plain    |              |                                                                                                          
 username | character varying(255) |                                                         | extended |              |                                                                                                          
 password | character varying(255) |                                                         | extended |              |                                                                                                          
Indexes:                                                                                                                                                                                                                          
    "users_pkey" PRIMARY KEY, btree (user_id)                                                                                                                                                                                     
                                                                                                                                                                                                                                  
 databasename=# 

4. Just choose the column which is going to be changed or to be renamed. For instance, the column named ‘password’ will be altered or changed into a column named ‘passwd’. So, the command for altering or changing it can be executed as shown as follows :

databasename=# alter table only users rename password to passwd;
ALTER TABLE
databasename=#

5. Make sure the column name has Just choose the column which is going to be changed or to be renamed. For instance, the column named ‘password’ will be altered or changed into a column named ‘passwd’. So, the command for altering or changing it can be executed as shown as follows :

databasename=# \d+ users                                                                                                                                                                                                                 
                                                        Table "public.users"                                                                                                                                                      
  Column  |          Type          |                        Modifiers                        | Storage  | Stats target | Description                                                                                              
----------+------------------------+---------------------------------------------------------+----------+--------------+-------------                                                                                             
 user_id  | integer                | not null default nextval('users_user_id_seq'::regclass) | plain    |              |                                                                                                          
 username | character varying(255) |                                                         | extended |              |                                                                                                          
 passwd   | character varying(255) |                                                         | extended |              |                                                                                                          
Indexes:                                                                                                                                                                                                                          
    "users_pkey" PRIMARY KEY, btree (user_id)                                                                                                                                                                                     
                                                                                                                                                                                                                                  
 databasename=# 

Leave a Reply