ALTER TABLE to Change Columns: Data Type

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:

ALTER TABLE contact
ALTER COLUMN opt_in
TYPE CHAR(1);

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:

ALTER TABLE contact
ALTER COLUMN opt_in
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.