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:

Execute the following command to confirm the service is running:

The results should resemble the following:

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:

An Explanation of the PostgreSQL ‘ALTER TABLE’ Statement Functions

The PostgreSQL ALTER TABLE syntax is as follows:

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:

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:

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

Now add values to the table with the following commands:

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

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:

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:

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

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:

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

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:

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

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.