January 22, 2025
Summary: This tutorial shows you step by step how to change the data type of a column by using the ALTER TABLE
statement.
Table of Contents
Introduction to PostgreSQL change column type
In a relational database, usually you should use the data type that best fits the representation of your data. However, in some applications, developers might store dates or numbers as text or dates as integers, in a mistake. This is not so uncommon, and ixing that could be quite a challenge as you need to cast from one data type to another when you want to change the data type used for a specific column. Depending on the current format of the data, it might be easy to fix or it can be complicated to fix.
PostgreSQL provides change column type statement to do that.
PostgreSQL change column type statement
To change the data type of a column, you use the ALTER TABLE statement as follows:
ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
Let’s examine the statement in a greater detail:
- First, specify the name of the table to which the column you want to change after the
ALTER TABLE
keywords. - Second, specify the name of the column that you want to change the data type after the
ALTER COLUMN
clause. - Third, supply the new data type for the column after the
TYPE
keyword. TheSET DATA TYPE
andTYPE
are equivalent.
To change the data types of multiple columns in a single statement, you use multiple ALTER COLUMN
clauses like this:
ALTER TABLE table_name
ALTER COLUMN column_name1 [SET DATA] TYPE new_data_type,
ALTER COLUMN column_name2 [SET DATA] TYPE new_data_type,
...;
In this syntax, you add a comma (,
) after each ALTER COLUMN
clause.
PostgreSQL allows you to convert the values of a column to the new ones while changing its data type by adding a USING
clause as follows:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type USING expression;
The USING
clause specifies an expression that allows you to convert the old values to the new ones.
If you omit the USING
clause, PostgreSQL will cast the values to the new ones implicitly. In case the cast fails, PostgreSQL will issue an error and recommends you provide the USING
clause with an expression for the data conversion.
The expression after the USING
keyword can be as simple as column_name::new_data_type
such as price::numeric
or as complex as a custom function.
PostgreSQL change column type examples
Let’s create a new table named assets
and insert some rows into the table for the demonstration.
CREATE TABLE assets (
id serial PRIMARY KEY,
name TEXT NOT NULL,
asset_no VARCHAR NOT NULL,
description TEXT,
location TEXT,
acquired_date DATE NOT NULL
);
INSERT INTO assets(name,asset_no,location,acquired_date)
VALUES('Server','10001','Server room','2017-01-01'),
('UPS','10002','Server room','2017-01-01');
To change the data type of the name
column to VARCHAR, you use the following statement:
ALTER TABLE assets
ALTER COLUMN name TYPE VARCHAR;
The following statement changes the data types of description
and location
columns from TEXT to VARCHAR
:
ALTER TABLE assets
ALTER COLUMN location TYPE VARCHAR,
ALTER COLUMN description TYPE VARCHAR;
To change the data type of the asset_no
column to integer, you use the following statement:
ALTER TABLE assets
ALTER COLUMN asset_no TYPE INT;
PostgreSQL issued an error and a very helpful hint:
ERROR: column "asset_no" cannot be cast automatically to type integer
HINT: You might need to specify "USING asset_no::integer".
The following statement adds the USING
clause to the above statement:
ALTER TABLE assets
ALTER COLUMN asset_no TYPE INT
USING asset_no::integer;
It worked as expected.
Advanced example
Let’s create a simple table named t1
and insert some rows into the table for the demonstration.
CREATE TABLE t1 ( a int, b text );
INSERT INTO t1(a, b)
VALUES(1, '0101-2019'),
(2, '0102-2019'),
(3, '0103-2019');
SELECT * FROM t1;
a | b
---+-----------
1 | 0101-2019
2 | 0102-2019
3 | 0103-2019
(3 rows)
ALTER TABLE t1 ALTER COLUMN b TYPE date USING (b::date);
ERROR: invalid input syntax for type date: "0101-2019"
As PostgreSQL has no idea how to do the conversion this will fail. But still you have the power of doing that by providing a function that does the conversion in exactly the way you want to have it:
CREATE OR REPLACE FUNCTION f_convert_to_date(pv_text IN text)
RETURNS date
AS $$
DECLARE
BEGIN
RETURN date(substr(pv_text, 6, 4) || substr(pv_text, 1, 4));
END;
$$ LANGUAGE plpgsql;
Of course you would add right logic to parse the input string, so that the function will return the matching date, such as the above function. Next, we can use this function to change the column type:
ALTER TABLE t1 ALTER COLUMN b TYPE date USING (f_convert_to_date(b));
The column was converted from text to date. We provided the exact way of doing that by calling a function that contains the logic to do that. As long as the output of the function conforms to the data type you want and you did not do any mistakes, you can potentially go from any source data type to any target data type.
Summary
In this tutorial, you have learned how to use the ALTER TABLE ALTER COLUMN
statement to change the type of a column.