How to Change/Modify Column Type in PostgreSQL?

In this tutorial, we will learn how we can change or modify the column data type or change the size of the data type in PostgreSQL. Along with the syntax and examples, we will learn the pros and cons of it. So, let’s get started!

Introduction

Creating a database and tables is the backbone of any server-side application. Therefore, you must create a schema diagram before creating an application.

However, in many cases, you do mistakes and create table columns that don’t fit in the application’s architecture. So, you need to change the column datatype or column size.

That is where the ALTER TABLE statement comes into the picture.

The ALTER TABLE is a DDL statement that is used to alter or modify the table structure.

In our case, we need the ALTER TABLE statement to modify the column type or column size.

Now let’s see the syntax of modifying the column type in PostgreSQL.

PostgreSQL Modify Column Type Syntax

Following is the syntax to modify the column data type in PostgreSQL.

Syntax:

ALTER TABLE [IF EXISTS] tableName
ALTER COLUMN columnName [SET DATA] type newDataType;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

• IF EXISTS – It checks if the table name that you mentioned exists. If not, it will not throw the error message.
• tableName – Any existing table name.
• columnName – The name of the column that you want to change the data type of.
• SET DATA TYPE – Here, SET DATA is an optional keyword. The ‘type’ keyword must be specified in order to set the new data type.
• newDataType – Any valid data type. If the existing data type is not convertible to the new data type, you will get an error message.

To modify the multiple columns’ data type, write multiple ALTER COLUMN statements separated by a comma.

I hope you have understood the syntax. Let’s see some examples now so that you will understand its real-time usage.

PostgreSQL Modify Column Type Examples

Before proceeding, we will need a table having columns of a variety of data types. So, let’s create one.

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    age INTEGER,
    fullname CHAR(20),
    prn_number VARCHAR(20)
);

INSERT INTO student (age, fullname, prn_number)
VALUES
    (20, 'John Doe', '12345'),
    (22, 'Jane Smith', '67890'),
    (19, 'Michael Johnson', '54321'),
    (21, 'Emily Williams', '98765'),
    (18, 'Alex Brown', '23456');

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we created a ‘student’ table having four different data types. Note that, although the ‘prn_number’ is a VARCHAR column, it consists of the numeric values. For the further demonstration purpose, we kept it as a VARCHAR.

Now let’s see some examples.

Change the Column Size

In the above table, the ‘fullname’ column has a length of 20 characters. However, some names might exceed this length. So, let’s increase the column size using the syntax we learned earlier.

ALTER TABLE IF EXISTS student
ALTER COLUMN fullname
SET DATA TYPE CHAR(30);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have set the same data type to the ‘fullname’ column, however, we have specified more length. Let’s see the table description and check if the size limit of the column is increased.

Note that, if you decrease the size limit of the column but the column already consists of values that have a length greater than the newly mentioned size (the reduced size), you will get an error as PostgreSQL doesn’t allow you to do so. In this case, you must update the values first from the table and then try the ALTER query again.

Note that, if any of the values in the column doesn’t have a length greater than the newly mentioned size, you won’t get any error.

Change the Column Type

When you try to change the column type, PostgreSQL checks if the type cast can be done implicitly. For example, the conversion from char to varchar is done implicitly.

Let’s see a similar example in which we will cast the ‘fullname’ column from the char type to varchar.

ALTER TABLE IF EXISTS student
ALTER COLUMN fullname
SET DATA TYPE VARCHAR(30);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the table description displays the column type as ‘character varying’ which is nothing but the varchar.

Change the Column Type Explicitly

In the above example, the existing and the new data type, both were of the same family, therefore PostgreSQL does the implicit conversion. However, what if the new data type is from a different group than the existing data type? In this case, we can use the explicit conversion which can be done using the USING keyword.

Note that, the explicit conversion may cause a loss of data integrity and unexpected outcomes. So, refer to the official documentation and take a backup of the data before moving to such steps.

If you see the ‘student’ table, you will realise the ‘prn_number’ column holds the numeric values but it has the varchar type. So, let’s cast it from varchar to integer.

ALTER TABLE student
ALTER COLUMN prn_number
SET DATA TYPE INT
USING prn_number::INT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the USING keyword is followed by the conversion expression. The :: operator is the same as the CAST function. The USING keyword might be needed in multiple conversions between two types such as text and numeric, boolean and numeric, date and text, etc.

As you can see, the prn_number column has been changed from the varchar to the integer.

Conclusion

In this tutorial, we have learned to modify the column types in PostgreSQL from and to different data types. Not only the conversion, but we also learned some important points such as implicit and explicit conversion, the possible data-integrity loss etc. We highly recommend not performing the steps on the production which might generate unexpected output. If you are a beginner, make sure you perform these kinds of operations under a trained and expert PostgreSQL database engineer.

Reference

PostgreSQL official documentation