In this article, we will discuss the step by step process of changing the data type of a column using the ALTER TABLE statement in PostgreSQL.
ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
Let’s analyze the above syntax:
- First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause.
- Second, give the name of column whose data type will be changed in the ALTER COLUMN clause.
- Third, provide the new data type for the column after the TYPE keyword. It is possible to use either SET DATA TYPE or TYPE.
Let’s create a table (say, assets) and insert a few rows into it for the demonstration using the below statement:
CREATE TABLE assets (
id serial PRIMARY KEY,
name TEXT NOT NULL,
asset_no VARCHAR NOT NULL,
acquired_date DATE NOT NULL
INSERT INTO assets (
Now we will change the data type of the
name column to VARCHAR, using the below statement:
ALTER TABLE assets ALTER COLUMN name TYPE VARCHAR;
Now we change the data type of description and location columns from TEXT to VARCHAR using the below statement:
ALTER TABLE assets
ALTER COLUMN location TYPE VARCHAR,
ALTER COLUMN description TYPE VARCHAR;
Now we check the table for the changes made using the below statement:
SELECT * FROM assets;
Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses
are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!