Online College Courses for Credit

ALTER TABLE to Change Columns: Data Type

ALTER TABLE to Change Columns: Data Type

Author: Sophia Tutorial

Identify a correctly constructed ALTER TABLE statement that changes the data type of a column.

See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.


what's covered
This tutorial explores using the ALTER TABLE statement to change the data type of a column in a table.

We may want to change the data type of a column. This generally is rare once data has been inserted in a column. When we make a change to the data type, PostgreSQL will cast the values to the new ones implicitly. In the case that casting the data type to the new one fails then the database will issue an error.

The structure of the command will look like the following:

ALTER TABLE <tablename>
ALTER COLUMN <columnname>
TYPE <newdatatype>;

Let us start with a basic contact table that was created to handle requests for an application:

CREATE TABLE contact( contact_id int PRIMARY KEY, username VARCHAR(50), password VARCHAR(50), opt_in int );

Let’s add some sample data to the table. We will cover these statements in a future tutorial:

INSERT INTO contact VALUES(1,'bob798','mypass1',1);
INSERT INTO contact VALUES(2,'jen558','mypass2',1);
INSERT INTO contact VALUES(3,'rand058','mypass3',1);

In this scenario, the organization originally wanted to have the application pass in 0 to opt-out and 1 to opt-in.

However, this may have been too challenging to decipher. Instead, they wanted to convert this to a character. To do so, we would execute the following command:


By running this query, no errors or issues would have come up. We can ignore the 0 and 1 for the opt_in column for now. Let’s add more sample data into the table using the updated data type:

INSERT INTO contact VALUES(4,'jeff768','mypass4','Y');
INSERT INTO contact VALUES(5,'sandra547','mypass5','Y');
INSERT INTO contact VALUES(6,'roberto9128','mypass6','Y');

If we tried to convert opt_in back to an int, we should get an error:

TYPE int;


This is due to having the character Y in the opt_in table on the last 3 records:


This is an issue to consider when we are changing the data type. Even though changing from an integer to a character did not create an error, if the opt_in had a value of two digits, we’d also run into an issue as the type that was converted to was set to one character. When we do change the data type, it is important to consider the data that the table already contains and what it should be changed to. If there isn’t any data in the table, it won’t be an issue to make those data conversions using the ALTER TABLE command.

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next try your own choices for which columns you want the query to provide.

Using the ALTER TABLE command, you can change the data type of columns but it does depend on what data is already inserted into the column.