Postgresql change column data type

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

We are going to cover the following topics:

• PostgreSQL change column type from integer to varchar
• PostgreSQL change column type from text to jsonb
• PostgreSQL change column type from integer to serial
• PostgreSQL change column type to boolean
• PostgreSQL change column type to integer
• PostgreSQL change column type to uuid
• PostgreSQL change column type to timestamp
• PostgreSQL change column type from integer to bigint
• PostgreSQL change column type from date to timestamp
• PostgreSQL change column type from text to json

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

first, Let’s create a table and insert some data into the table then follow this tutorial to change the data type of 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);

PostgreSQL change column type from integer to varchar

In PostgreSQL, to change a column 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 below command:

ALTER TABLE books 
ALTER COLUMN record_no TYPE VARCHAR;

Now check the data type of column record_no.

Read PostgreSQL TO_TIMESTAMP function

PostgreSQL change column type from text to jsonb and json

• In Postgresql, jsonb format is like JSON which stores data in a form of key/value pairs.
• The difference is the JSON stores an exact copy of the data or input where as jsonb stores in binary format.

Let’s change the column description to jsonb :

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

In the above output, 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, just replace jsonb with josn.

PostgreSQL change 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 as 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 values of column record_no from null to some integer value of both records in the books table by using:

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

Next, insert new value into 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, column record_no will automatically update itself to a new serial value.

Read: How to create a table in PostgreSQL

PostgreSQL change column type to boolean

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

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

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

Read: How to connect to PostgreSQL database

PostgreSQL change column type to integer

In Postgresql, to change the data type of column to integer, use the below command:

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

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

Read: How to Restart PostgreSQL

PostgreSQL change column type to uuid

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

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

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

PostgreSQL change column type from integer to bigint

• Bigint is like an integer but there is a difference in representing bits.
• The difference is the integer accepts 4 bytes signed integer and bigint accepts 8 bytes signed integer.

Let’s change the record_no column type to bigint.

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

In the above output, you have noticed that there is no difference between integer and big int both showing column type 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

PostgreSQL change column type from date to timestamp

• In Postgresql, before changing the data type of the column, let’s explore ” what is 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 below 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;

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 primary column name in Postgresql

So in this tutorial, we have learned different data type that exists in Postgresql and changed the column data type using these data type.

We have covered the following topic:

• PostgreSQL change column type from integer to varchar
• PostgreSQL change column type from text to jsonb
• PostgreSQL change column type from integer to serial
• PostgreSQL change column type to boolean
• PostgreSQL change column type to integer
• PostgreSQL change column type to UUID
• PostgreSQL change column type to timestamp
• PostgreSQL change column type from integer to bigint
• PostgreSQL change column type from date to timestamp
• PostgreSQL change column type from text to JSON