Alter Table by Changing Column Datatype in PostgreSQL

Introduction

This tutorial will explain how to use the PostgreSQL alter a table statement to add or change data in a PostgreSQL table. The PostgreSQL ALTER TABLE statement can be used to change how a table was initially structured. This is a very useful function during database audits, software development or accommodating various business requirement.

Prerequisites for using the PostgreSQL Alter Table Statement

  • The PostgreSQL server must be properly installed, configured and running. Download PostgreSQL for Linux and Windows systems here

Execute the following command to start the PostgreSQL server on a LINUX system:

sudo service postgresql start

Execute the following command to confirm the service is running:

service postgresql status

The results should resemble the following:

â— postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)

Execute the following operations, in sequence, to start, stop and restart the PostgreSQL server on a Windows machine:

  1. Open the “Control Panel”
  2. Open “Administrative Tools”
  3. Open “Services”
  4. Locate the “PostgreSQL Server” service
  5. Start, stop or restart the service as shown in the left-side panel of the following screenshot:

Restarting PostgreSQL server in a Windows Machine

An Explanation of the PostgreSQL ‘ALTER TABLE’ Statement Functions

The PostgreSQL ALTER TABLE syntax is as follows:

ALTER TABLE the_table_name action;

The PostgreSQL ALTER TABLE statement provides the action clause that produces a number of options:

  • SET DATA TYPE allows for modification the data type of a table’s column.
  • RENAME will change only the name of the target column, and will not affect any stored data.
  • ADD COLUMN allows for adding a new column to the table. This can be used in a similar fashion as the PostgreSQL CREATE TABLE command.
  • DROP COLUMN will allow removal of a single or multiple columns within a table.

How to Combine the PostgreSQL Change Column Data type Statement with the ‘Action’ ‘SET DATA TYPE’

The previous section explained the various PostgreSQL ALTER TABLE functions. This section will cover how to pair that function with the action SET DATA TYPE.

Execute the following ALTER COLUMN your_column_name [SET DATA] TYPE with the_new_data_type command to modify the data type of a specific column within a table:

ALTER TABLE the_table_name
ALTER COLUMN the_column_name [SET DATA] TYPE the_new_data_type;

Following is a step-by-step breakdown of the above statement:

  • The column’s table name that will be modified is indicated using the ALTER TABLE clause.

  • The name of the field or column that will have the data type changed is inserted after the ALTER COLUMN clause.

  • Lastly, the new required data type for the designated column is given after the keyword “TYPE.” Note the either SET DATA TYPE or just TYPE may be used interchangeably as keywords for this function.

How to Create a Sample Data Set

This section will show how to create a sample data set to use in the following segments of this tutorial. Below is an example of how the PostgreSQL character types work in the PostgreSQL database. The following commands will create a table named hardware that will be used for a hardware inventory system:

CREATE TABLE hardware (
hw_id serial PRIMARY KEY,sp
hw_name TEXT NOT NULL,
hw_no VARCHAR NOT NULL,
hw_description TEXT,
loc_deployment TEXT
);

To confirm the table was created successfully, execute the following \d command against the table hardware:

Table "public.hardware"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+-----------------------------------------
hw_id | integer | | not null | nextval('hardware_hw_id_seq'::regclass)
hw_name | text | | not null |
hw_no | character varying | | not null |
hw_description | text | | |
loc_deployment | text | | |
Indexes:
"hardware_pkey" PRIMARY KEY, btree (hw_id)

Now add values to the table with the following commands:

INSERT INTO hardware (
hw_name,
hw_no,
hw_description,
loc_deployment
)
VALUES
(
'PC01',
'123',
'President Workstation',
'ROOM 211'
),
(
'PC02',
'124',
'VP workstation',
'ROOM 211B'
);

Confirm the INSERT operation was successful by executing the SELECT query. If successful, the results should resemble the following:

testdatabase=# SELECT * FROM hardware;
hw_id | hw_name | hw_no | hw_description | loc_deployment
-------+---------+-------+-----------------------+----------------
1 | PC01 | 123 | President Workstation | ROOM 211
2 | PC02 | 124 | VP workstation | ROOM 211B
(2 ROWS)

The following section will provide a demonstration of how to use the PostgreSQL ALTER TABLE SET DATA TYPE command.

How to Change the PostgreSQL Column Data Type

The previous section created the following hardware table:

Table "public.hardware"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+-----------------------------------------
hw_id | integer | | not null | nextval('hardware_hw_id_seq'::regclass)
hw_name | text | | not null |
hw_no | character varying | | not null |
hw_description | text | | |
loc_deployment | text | | |
Indexes:
"hardware_pkey" PRIMARY KEY, btree (hw_id)

As an example of how to change data in the table, execute the following statement to alter the loc_deployment data type from TEXT to VARCHAR:

ALTER TABLE hardware ALTER COLUMN loc_deployment TYPE VARCHAR;

Execute the \d command against the hardware table to confirm the data was successfully changed. The results should resemble the following:

Table "public.hardware"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+-----------------------------------------
hw_id | integer | | not null | nextval('hardware_hw_id_seq'::regclass)
hw_name | text | | not null |
hw_no | character varying | | not null |
hw_description | text | | |
loc_deployment | character varying | | |
Indexes:
"hardware_pkey" PRIMARY KEY, btree (hw_id)

Note that the loc_deployment data type was successfully changed from TEXT to VARCHAR.

For another practice example, execute the following statement to change the VARCHAR to INT datatype:

ALTER TABLE hardware ALTER COLUMN hw_no TYPE INT;

The above statement should return an error message that resembles the following:

ERROR: column "hw_no" cannot be cast automatically to type integer
HINT: You might need to specify "USING hw_no::integer".

The above error message indicates the VARCHAR type cannot be changed to an integer with this command. To perform this function, PostgreSQL recommends executing the USING clause as shown in the following statement:

ALTER TABLE hardware ALTER COLUMN hw_no TYPE INT USING hw_no::INTEGER;

Verify the above statement was successful by executing a \d hardware command. The results should resemble the following.

TABLE "public.hardware"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
----------------+-------------------+-----------+----------+-----------------------------------------
hw_id | INTEGER | | NOT NULL | NEXTVAL('hardware_hw_id_seq'::regclass)
hw_name | text | | NOT NULL |
hw_no | INTEGER | | NOT NULL |
hw_description | text | | |
loc_deployment | CHARACTER VARYING | | |
Indexes:
"hardware_pkey" PRIMARY KEY, btree (hw_id)

Note that the hw_no column had its data type successfully changed from VARCHAR to INT.

Conclusion

This tutorial explained how to use the PostgreSQL alter a table statement to add or change data in a PostgreSQL table. The article covered how to download and run PostgreSQL for both Linux and Windows systems. An explanation of the PostgreSQL ALTER TABLE statement functions were provided along with a step-by-step breakdown of those functions. The tutorial also covered how to create the sample data set, confirm the table was created successfully and how to add and change column data type. Remember that data types cannot be changed to an integer with the ALTER TABLE table name ALTER COLUMN hw_no TYPE INT command, but must be done by executing the USING clause as recommend by PostgreSQL.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.