Change the Datatype of a Column in SQL

In SQL, a datatype is the type of data that we can store in a database. The data type specifies the type and size of data to be stored in the tables present in the database. The different data types used in SQL are based on the requirement. The data types are character data types, numeric data types, Boolean data types, data and time data types, and binary data types.

The numeric data types contain INT, DECIMAL, FLOAT, and BIGINT. The character data types The Boolean data type contains BOOLEAN. The binary data types contain BINARY, BLOB, and VARBINARY. The date & time data types contain TIME, DATE, TIMESTAMP, and DATETIME. The character data types contain CHAR, TEXT, and VARCHAR.

Sometimes, we need to convert the data type contained in the database, which can be effortlessly changed in MySQL. In this article, we are going to understand the different methods of converting data types in SQL, but before that, let us discuss some of the reasons why we need to change data types in SQL.

Need to change the data type in SQL:

• If you choose the suitable data type, it will ensure data integrity and accuracy. You can apply constraints and prevent the storage of invalid data. For example, if a column can store numeric values, then the numeric data type (such as INT, BIGINT, FLOAT, etc.) makes sure that only valid numeric data is kept in that column.
• If you choose the right data type, it can help optimize storage space. For example, if you have a column that stores small integers, you can use smaller data types such as TINYINT instead of INT, which can significantly reduce storage requirements. It is beneficial for large databases or when working with limited storage resources.
• The choice of data type can affect query performance. Various data types have various storage necessities, so selecting the suitable data type is essential, which can improve query performance. For example, using a fixed-length data type (like CHAR) for columns with contiguous-length values may be more efficient than using a variable-length data type (like VARCHAR).
• When integrating data from different systems, it is common to encounter data with different formats and types. Changing data types lets you standardize data, making it compatible and consistent across databases. It makes sure seamless data processing and analysis.
• Data type conversions may be essential to validate and convert data. For example, changing a string to a date or timestamp data type permits you to perform date-related calculations accurately. Similarly, changing a string to a numeric data type enables mathematical operations on the data.
• Sometimes, the needs of an application or specific use cases may demand a change in the data types. For example, if you require to store large text documents, you can choose a data type such as TEXT or VARCHAR(MAX) instead of VARCHAR to accommodate long strings.

Examples of various methods to change the datatype of a column in SQL:

Changing the data type of a column using MySQL Workbench:

MySQL Workbench is a graphical user interface used to work with MySQL databases. We will be changing the data type of the column in MySQL Workbench.

Syntax to alter the datatype of a column in MySQL Workbench:

Let us consider a table named student_table, which contains the fields of number, name, age, address, stream, and marks.

Now, we will describe the datatypes of the columns of the above table using the following command:

After describing the table, it will look like it is shown below:

Example 1: Changing the datatype of the column named 'marks' of student_table.

We will change the datatype of the column called 'marks' of student_table using the following command:

Now, we will again describe the data type of the column to check whether the data type of the column named 'marks' has been changed from VARCHAR to INT. We will use the following command:

As you can see below, the data type of the column named 'marks' has been changed successfully.

Example 2: Changing the datatype of the column named 'address' of the student_table.

Let us change the datatype of the column called 'address' in student_table. Use the given command to change the datatype:

Now, we will describe the data type of the column to check whether the data type of the column named 'marks' has been changed or not. We will use the following command to describe the datatype of the column:

As you can see below, the data type of the column named 'address' has been changed from CHAR to VARCHAR successfully.

Thus, you can alter the datatype of a column in a table using MySQL Workbench.

Changing the datatype of a column using PostgreSQL:

PostgreSQL is an open-source object-relational database system that is a highly secure and trustworthy database. There are two ways to convert the datatype of a column in PostgreSQL using different syntaxes:

Syntax 1: Using ALTER TABLE command with ALTER COLUMN command

Syntax 2: Using type cast operator (::)

Let us consider a table named 'Employee', which contains the fields of EmpId, EmpName, EmpAddress, EmpDepartment, and EmpSalary.

Before changing data types, we will look at a table in PostgreSQL. To view the table, we will use the following command:

Now, you can see that the data types appear next to the column names, as shown below.

Example 1: Changing the data type of a column named 'EmpName' of the 'Employee' table using syntax 1.

We will use the following command to change the datatype of the column named 'EmpName' of the 'Employee' table.

When the command is executed, it displays the message that you can see below.

To view the table again, we will use the following command:

Now, you can see that the data type of the column named 'EmpName' has been changed from VARCHAR to TEXT.

Example 2: To change the datatype of a column named 'Employee' of table 'Employee' using syntax 2.

We will use the following command to change the datatype of the column named 'EmpSalary' of the 'Employee' table.

When the command is executed, it displays the following message.

To see the table, we will use the given command:

Now, you can see that the data type of the column named 'EmpSalary' has been changed from VARCHAR to INT.

Changing the data type of a column using Microsoft SQL Server:

Microsoft SQL Server is a relational database management system that uses SQL language to manage and search databases. We will learn to convert the column's datatype in Microsoft SQL Server.

Syntax:

Let us consider a table named 'Customer', which contains fields for ID, First Name, Last Name, Age, and Address.

Before making any changes to the table, its data types appear as shown below:

Example 1: Changing the data type of a column named 'FirstName' in the 'customer' table.

We will alter the data type of the column named 'FirstName' in the 'Customer' table. Use the given command to change the datatype:

After executing the above command, the successfully executed query message will be displayed.

You see below that the data type of column 'FirstName' has been changed from VARCHAR to TEXT successfully.

Example 2: Changing the datatype of a column named 'Age' of the 'Customer' table.

Let us change the datatype of the column called 'Age' in the 'Customer' table. Use the given command to change the datatype:

After executing the above command, you will see a query executed successfully message on the screen.

You see below that the data type of column 'Age' has been converted from BIGINT to INT successfully.

Conclusion:

In this article, you have understood that datatype is the format of data that is stored in the database. You have learned how to change the data type of a column in SQL and the need to change the data type of a column in a database.

You have encountered several ways to change the datatype of a column in different databases, such as PostgreSQL, Microsoft SQL Server, and MySQL Workbench.