Introduction

This is an article specifically describe about how to rename column in PostgreSQL Database. It is one of the basic task that should available in any type of database. In this article, the target for renaming the column is for the PostgreSQL database. The following description will enlist all of the steps to perform the task.

Renaming Column Steps

So, in order to rename a column as part of a table inside a certain database, those steps are :

1. Connecting directly to PostgreSQL Database.

This step is the first step before doing any other steps further. It is connecting to the PostgreSQL Database. In this article’s context, the demonstration of the connection process is in the CLI (Command Line Interface). So, just type the following command in order to connect to the PostgreSQL Database :

psql -Uuser dbname

For an example :

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

2. Don’t forget to connect to the database. It is the corresponding database where the table has the available column for further renaming process. The following is the command for listing the available database which is by typing ‘\l’ :

postgres=# \l
                                 List of databases
    Name    | Owner    | Encoding |   Collate   |    Ctype    |   Access privileges    
------------+----------+----------+-------------+-------------+------------------------
 db1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db2        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db3        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
            | postgres |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
            | postgres |          |             |             | postgres=CTc/postgres
... 
(xx rows)
postgres=# 

After listing the database, connect to the associated database by typing the following command :

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

3. Next, list the table in the connected database. Do it by typing the following command :

db1=# \d+
                                            List of relations
 Schema |                       Name                       |   Type   | Owner    |    Size    | Description 
--------+--------------------------------------------------+----------+----------+------------+-------------
 public | user                                             | table    | postgres | 16 kB      | 
 public | user_id_seq                                      | sequence | postgres | 8192 bytes | 
...
(xx rows)

4. The most important step above all. List all the columns available in the corresponding table. Do it by executing the following command :

db1=# \d+ user

Don’t forget to check the description of the table again as follows :

db1=# \d+ user;
                                                               Table "public.activity"
   Column    |          Type          | Collation | Nullable |                    Default                    | Storage  | Stats target | Description 
-------------+------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 user_id     | integer                |           | not null | nextval('user_id_seq'::regclass)              | plain    |              | 
 name        | character varying(255) |           | not null |                                               | extended |              | 
 password    | text                   |           |          |                                               | extended |              | 
Indexes:
    "user_pkey" PRIMARY KEY, btree (user_id)
db1=#

5. Finally, after listing all the available columns inside the table, just execute the following command for renaming the corresponding column :

postgres=# ALTER TABLE user rename user_id to id;
ALTER TABLE
postgres=# 

6. Last but not least, re-execute again the command for listing the available columns inside the database. Do it by typing the following command or query :

db1=# \d+ user;
                                                               Table "public.user"
   Column    |          Type          | Collation | Nullable |                    Default                    | Storage  | Stats target | Description 
-------------+------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 id          | integer                |           | not null | nextval('user_id_seq'::regclass)              | plain    |              | 
 name        | character varying(255) |           | not null |                                               | extended |              | 
 password    | text                   |           |          |                                               | extended |              | 
Indexes:
    "user_pkey" PRIMARY KEY, btree (user_id)
db1=#

Appearing as the output of the above, the column name has already changed from ‘user_id’ to ‘id’. The process for renaming the column name is a success.