Change the Data Type of a Column in SQL

1. Introduction

Changing the data type of a column in SQL is a common task for database administrators and developers.

This operation is necessary when an existing column’s data type is too restrictive or too broad. It’s also required when the nature of the data within the column changes over time. SQL provides several methods, depending on the database management system used.

In this article, we’ll look at different ways to perform this action in SQL Server, PostgreSQL, and MySQL.

2. Set Up

We’ll use the University database and Course table to discuss the queries in this article.

The Course table contains an integer column credits. We’ll write the queries to alter this column to use decimal data type instead of integers.

Also, while making structure changes in production, it is essential to back up the current table, as some DDL operations are irreversible.

3. PostgreSQL

In PostgreSQL, we can use the ALTER statement to change a column’s datatype. Let’s explore how conversions work with compatible and non-compatible types.

3.1. Compatible Types

Compatibility of types means that PostgreSQL can implicitly convert between them even if the table already contains data. For example, that’s the case with INTEGER and DECIMAL. The conversion of an integer column to a decimal one is lossless, whereas converting from DECIMAL to INTEGER may result in some precision loss due to rounding.

Let’s look at how to convert from INTEGER to DECIMAL for the credits column in the Course table:

ALTER TABLE course 
ALTER COLUMN credits TYPE DECIMAL;

We used the TYPE keyword to define the new column type. Since the old and new types are compatible, the conversion will succeed even if the table already contains data.

Next, let’s change the column type to VARCHAR and observe how it behaves:

ALTER TABLE course 
ALTER COLUMN credits TYPE VARCHAR;

This query works because the DECIMAL type can be implicitly converted to VARCHAR.

3.2. Non-Compatible Types

Not all types are compatible with direct conversion. Let’s attempt to convert the VARCHAR column back to a decimal type:

ALTER TABLE course 
ALTER COLUMN credits TYPE DECIMAL;

This query fails with the error:

ERROR: column "credits" cannot be cast automatically to type numeric

We can address this compatibility issue with the USING keyword, which allows us to convert between incompatible types.

3.3. The USING Clause

The USING clause with the :: operator allows us to change a column’s data type while transforming existing values during the ALTER command:

ALTER TABLE course 
ALTER COLUMN credits TYPE DECIMAL
USING credits::INTEGER;

Based on the column value, we can use more complex expressions in the USING clause. For example, let’s update one of the columns with an alphanumeric value:

UPDATE course set credits = '7abc' where id = 'CS111'

The conversion with USING fails since 7abc can’t be converted to a numeric value. We have to specify the transformation to change the type:

ALTER TABLE course 
ALTER COLUMN credits TYPE INTEGER
USING regexp_replace(credits, '\D', '', 'g')::INTEGER;

We extracted the numeric value with the regexp_replace function before casting it to an integer in the USING clause.

This USING clause is supported in PostgreSQL but not in MySQL and SQL Server, where this process requires creating new columns and updating the values separately.

4. MySQL

MySQL provides two ways to change the datatype of a column.

4.1. Using MODIFY

We can use the clause MODIFY with an ALTER statement to change the datatype of a column. It follows the syntax:

MODIFY COLUMN <column-name> <new-type>

Let’s write the query to change the type of credits column:

ALTER TABLE Course 
MODIFY COLUMN credits DECIMAL(10,2);

ALTER changes the type of the credits column to DECIMAL.

4.2. Using CHANGE

Alternatively, we can use the CHANGE clause instead of MODIFY to perform the same action:

ALTER TABLE Course
CHANGE COLUMN credits credits DECIMAL(10,2);

The CHANGE clause follows the syntax:

CHANGE COLUMN <old-column-name> <new-column-name> <new-type>

The CHANGE clause renames a column and alters its definition in a single step, while the MODIFY clause alters the definition of a column without changing its name.

In our example, since we don’t want to rename the column, we specified the same name for the old and new columns.

Unlike PostgreSQL, MySQL can implicitly convert a VARCHAR column to an INTEGER type using a CHANGE or MODIFY clause without explicit cast operation. However, if the VARCHAR column contains values that aren’t valid numbers, the ALTER query will fail.

5. SQL Server

SQL Server also lets us change the datatype of a column using the ALTER command:

ALTER TABLE Course
ALTER COLUMN credits DECIMAL;

This command modifies the credits column to have the DECIMAL datatype.

SQL Server handles such conversions gracefully if the data in the column is compatible with the new type. For instance, converting from INTEGER to DECIMAL is straightforward and doesn’t require special handling.

SQL Server can implicitly convert a VARCHAR to DECIMAL if the column contains only valid numeric values. Otherwise, it fails just like in MySQL

6. Conclusion

In this article, we explored how to change a column’s data type across different database management systems: MySQL, PostgreSQL, and SQL Server. Each system offers unique methods for altering column types, with PostgreSQL providing advanced features like the USING clause for type conversion.