This is an article about how to alter table in PostgreSQL Database Server by executing the correct query in PostgreSQL Database Command Console. The main goal is to change a column that exist in the table located in a specific database. Below are the steps taken in order to achieve or to accomplish on changing or altering the column of the table :
1. Login to the PostgreSQL Database Server.
In this article, the method which is used to login to the PostgreSQL Database Server in this context clearly to utilize the PostgreSQL Command Console via command line. Below is normally the login process :
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. The next step is to be able to execute the command for altering or changing the column of the table. For instance, the following has a table where the name of the table is ‘users’. All the tables exist in the database can be listed 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. After confirming that the right table exist in the database, start execute the command for altering or changing the column of the table. But before go on to the process of altering or changing it, just make sure the right column exist. It can be done by typing the following command :
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. For instance, the column named ‘password’ will be altered or changed its type by reducing the size of the character type from 255 into 32. It is done by executing the following command in the PostgreSQL command line :
databasename=# alter table users alter column password type varchar(32);
When the command is executed successfully, just check the result by re-execute the following command :
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(32) | | extended | |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
databasename=#
As it can be seen, the type of the column has already changed. It has changed from the type of ‘character varying(255)’ to the type of ‘character varying(32)’.