PostgreSQL change column data type

In this PostgreSQL tutorial, we will learn how to change column type in PostgreSQL from one data type to another.

PostgreSQL change column data type

Suppose you are an employee of XYZ company in the USA, and in front of you is a situation to change the data type of a column of a database. So, here in this section, we will learn ‘How to change the column type from one data type to another.

First, let’s create a table and insert some data into it. Then, follow this tutorial to change the data type of the column.

CREATE TABLE books (
id serial PRIMARY KEY,
name TEXT NOT NULL,
boo_no VARCHAR NOT NULL,
description TEXT,
shelf_location TEXT,
purchased_date date,
record_no INT,
availbility TEXT);

Approach 1: Basic ALTER TABLE Syntax (The Standard Approach)

The most straightforward way to change a column’s data type is to use the ALTER TABLE command with the ALTER COLUMN clause. This is my go-to method for simple conversions in smaller tables.

Syntax

ALTER TABLE table_name 
ALTER COLUMN column_name 
TYPE new_data_type;

Example 1: Changing column type from integer to varchar

In PostgreSQL, to change a column’s data type, we use the ALTER TABLE statement. Let’ change the column record_no data type of the above table to varchar.

Now, to change the column, we will use the following command:

ALTER TABLE books 
ALTER COLUMN record_no TYPE VARCHAR;

Now, check the data type of the column record_no.

When Does the Simple Approach Work?

The basic syntax works seamlessly when:

1. The conversion between types is implicitly possible (like INTEGER to BIGINT)
2. The table is relatively small (under a million rows in most cases)
3. You can afford a brief table lock during the conversion

Read PostgreSQL TO_TIMESTAMP function.

Approach 2: ALTER TABLE with USING Clause (For Complex Conversions)

When dealing with complex type conversions, I always use the USING clause to explicitly instruct PostgreSQL on how to convert existing values to the new type.

Syntax

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE new_data_type
USING expression;

Example 1: Changing the column type from text to jsonb and json

• In PostgreSQL, the JSONB format is similar to JSON, which stores data in the form of key-value pairs.
• The difference is that JSON stores an exact copy of the data or input, whereas JSONB stores the data in a binary format.

Let’s change the column description to jsonb :

ALTER TABLE books 
ALTER COLUMN description type jsonb using description::jsonb;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

In the above output, the description data type changed to jsonb.

• And at the last line of query, where type jsonb, using clause is used to specify the column type, using column_name::datatype
• if you want to change column type to json, replace jsonb with json.

Example 2: Changing the column type from integer to serial

In PostgreSQL, the Serial data type allows us to generate unique integer numbers (IDs, identity, auto-increment, sequence) for a column in a table.

To make the column serial, use the following command:

• Create sequence

CREATE SEQUENCE books_table_record_no_seq
        INCREMENT 1
        MINVALUE 0
        MAXVALUE 3 START 0
        CACHE 1;

Then we will alter the column and convert record_no to serial:

ALTER TABLE books ALTER COLUMN record_no
        SET DEFAULT nextval('books_table_record_no_seq'::regclass);

Update the values of the column record_no from null to some integer value in both records in the books table by using:

UPDATE books
SET record_no = 0 
WHERE book_no='F-21';

Next, insert a new value into the Postgres table books:

INSERT INTO books(name,book_no,shelf_location,purchased_date)values('Path To Success','F-23','S2-R1','2019-02-12');

After running the above query, the column record_no automatically updated itself to a new serial value.

Read: How to create a table in PostgreSQL

Example 3: Changing column type to boolean

In PostgreSQL, Boolean is a kind of data type that stores true or false values. However, before changing the column type, let’s check the data type of the availability column in the books table.

Let’s change the data type of the ‘ availability’ column to boolean.

ALTER TABLE books 
ALTER COLUMN availbility type boolean using availbility::boolean;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Read: How to connect to a PostgreSQL database

Example 5: Changing column type to integer

In PostgreSQL, to change the data type of a column to integer, use the following command:

ALTER TABLE books 
ALTER COLUMN record_no type integer using record_no::integer;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

In the above output, we have changed the data type of the column record_no to an integer.

Read: How to Restart PostgreSQL

Example 5: Changing column type to uuid

• In PostgreSQL, we sometimes enter the UUID of a column as text; however, PostgreSQL supports the UUID data type.
• UUID stands for Universal Unique Identifier. A UUID value is a 128-bit quantity generated by an algorithm that makes it unique in the known universe using the same algorithm.

Let’s change the data type of the book_order_id column to UUID again.

ALTER TABLE books 
ALTER COLUMN book_order_id type uuid using book_order_id::uuid;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Example 6: Changing the column type from integer to bigint

• BigInt is like an integer, but there is a difference in representing bits.
• The difference is that the integer accepts 4 4-byte signed integers, and bigint accepts 8 8-byte signed integers.

Let’s change the record_no column type to bigint.

ALTER TABLE books 
ALTER COLUMN record_no type bigint using record_no::bigint;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

In the above output, you will notice that there is no difference between integer and big int, as both display the column type as ‘integer’.

You can find the difference by storing a large number of value, integer stores small values but bigint stores large.

Read: PostgreSQL INSERT INTO table

Example 7: Changing the column type from date to timestamp

• In PostgreSQL, before changing the data type of the column, let’s explore ” what is a timestamp?“ It is a temporal data type in PostgreSQL that stores both date and time, but doesn’t store time zone data.

As a result, when we change the timezone in the database server, the timestamp value stored in the database will not change automatically.

To change the date data type to timestamp, use the following command.

Let’s change the purchased_date column data type to timestamp.

ALTER TABLE books 
ALTER COLUMN purchased_date type timestamp using purchased_date::timestamp;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Key Benefits of the USING Clause

• Explicit control: You define exactly how the conversion happens
• Data transformation: You can clean or format data during the type change
• Error prevention: Properly handle conversions that might otherwise fail

Conclusion

Changing column data types in PostgreSQL requires careful consideration of your specific requirements. For small tables or dev environments, the simple ALTER TABLE command works perfectly. For larger tables or production environments, you can use the other approaches as explained in this article.

You may like reading the following articles.

• PostgreSQL ALTER TABLE
• PostgreSQL length of string
• PostgreSQL ADD COLUMN
• PostgreSQL DATE Functions
• PostgreSQL cast int
• PostgreSQL import SQL file
• How to find the primary column name in PostgreSQL