Cast Function in SQL

Hello. In this tutorial, we will learn the CAST function in SQL. For illustration, we will use the Postgresql database running on Docker.

1. Introduction

Data type conversion is an essential aspect of working with data in SQL. Here are some reasons why data type conversion is important:

• Data Integrity: Ensuring that data is stored in the appropriate data type helps maintain data integrity. Each column in a database table is defined with a specific data type, and when data is inserted or updated, it should match the defined type. Performing data type conversions allows you to validate and enforce the correct data types, preventing data corruption and inconsistencies.
• Data Analysis and Reporting: Data type conversion enables effective data analysis and reporting. Different data types have different characteristics and behaviors. By converting data to the appropriate types, you can perform calculations, aggregations, and comparisons accurately. For example, converting a string representing a date to a date data type allows you to perform date-related operations and filtering efficiently.
• Query Performance: Using the correct data types in queries can significantly impact performance. When data types are mismatched in a query, the database may need to perform implicit conversions, which can lead to slower execution times. Explicitly converting data to the appropriate types beforehand ensures that queries can utilize indexes, optimize execution plans, and reduce unnecessary conversions during query processing.
• Compatibility and Interoperability: Data type conversion is crucial when working with data from different sources or when integrating databases with different schemas. Converting data to a common data type facilitates data exchange and interoperability between systems. It ensures that data can be properly interpreted and processed across different platforms, applications, or databases.
• Data Validation and Error Handling: Data type conversion allows for data validation and error handling. When data is received or entered into a system, it is crucial to verify that it meets the expected data type. Converting data types can help identify and handle invalid or malformed data by raising errors or providing default values in case of conversion failures.
• Storage Optimization: Choosing the appropriate data type can help optimize storage space. Some data types, such as integers or booleans, require less storage compared to string or floating-point types. By converting data to more space-efficient types without sacrificing accuracy or functionality, you can reduce storage requirements and improve database performance.

1.1 Overview and Syntax of the CAST Function

In PostgreSQL, the CAST function is used to explicitly convert a value of one data type to another data type. It allows you to change the data type of an expression or a column to perform specific operations or to ensure compatibility in query results. The basic syntax of the CAST function in PostgreSQL is as follows:

Command Syntax

CAST(expression AS target_data_type)

Here, expression is the value you want to convert, and target_data_type is the desired data type to which you want to convert the expression.

1.2 Supported data types for conversion

PostgreSQL supports a wide range of data types for conversion using the CAST function or other conversion functions. Here are some of the commonly used data types for conversion in PostgreSQL:

These are just a subset of the supported data types in PostgreSQL. You can refer to the PostgreSQL documentation for a comprehensive list of all the data types available in PostgreSQL and their specific usage and syntax. It’s worth noting that PostgreSQL also provides type casting using the :: syntax, where you can cast a value to a specific data type without using the CAST function explicitly. For example:

SELECT '123'::integer;

This will convert the string ‘123’ to an integer using type casting.

1.3 Advantages vs. Disadvantages

2. Examples and Illustrations

Let us dive into some practice implementation on the postgresql database.

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.

Docker commands

-- command to run postgres on docker –

-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.

2.2 Converting a string to an integer

In PostgreSQL, you can convert a string to an integer using the CAST() function. Here’s an example:

Example 1

select
	cast('123' as INTEGER);

In this example, the string value ‘123’ is cast to the INTEGER data type using the CAST() function. The result will be the integer value 123.

2.3 Converting a decimal to a string

In PostgreSQL, you can convert a decimal to a string using the CAST() function. Here’s an example:

Example 2

-- pre-requisite
create table sample_table (
  decimal_column DECIMAL(10,
2)
);

insert
	into
	sample_table (decimal_column)
values (123.45);

-- query
select
	cast(decimal_column as text)
from
	sample_table;

In this example, decimal_column represents the column or decimal value you want to convert to a string. By applying the CAST() function with the target data type TEXT, the decimal value will be converted to a string representation. The result will be a string.

2.4 Converting a date to a different format

In PostgreSQL, you can convert a date to a different format using the TO_CHAR() function. Here’s an example:

Example 3

-- pre-requisite
create table your_table (
  your_date_column DATE
);

insert
	into
	your_table (your_date_column)
values ('2023-06-13');

-- query
select
	TO_CHAR(your_date_column,
	'YYYY-MM-DD')
from
	your_table;

The TO_CHAR() function allows you to specify the desired format for the date conversion. The format pattern ‘YYYY-MM-DD’ is just an example. You can use various format patterns to represent the date in different formats according to your requirements. For instance, ‘YYYY-MM-DD’ represents the date in the format ‘YYYY-MM-DD’ (e.g., ‘2023-06-13’). Here are a few commonly used format patterns for date conversion:

• ‘YYYY-MM-DD’ – Year, month, and day (e.g., ‘2023-06-13’)
• ‘MM/DD/YYYY’ – Month, day, and year (e.g., ’06/13/2023′)
• ‘DD-Mon-YYYY’ – Day, abbreviated month name, and year (e.g., ’13-Jun-2023′)
• ‘Mon DD, YYYY’ – Abbreviated month name, day, and year (e.g., ‘Jun 13, 2023’)

You can refer to the PostgreSQL documentation for more format patterns and options available with the TO_CHAR() function.

2.5 Converting a boolean value to an integer

In PostgreSQL, you can convert a boolean value to an integer using the ::integer cast or the CAST() function. Here’s an example:

Example 4

-- pre-requisite
create table your_table (
  boolean_column BOOLEAN
);

insert
	into
	your_table (boolean_column)
values (true);

insert
	into
	your_table (boolean_column)
values (false);

insert
	into
	your_table (boolean_column)
values (true);

-- query
select
	boolean_column::integer
from
	your_table;

In this example, boolean_column represents the column containing the boolean value, and your_table is the name of the table where the column resides. The ::integer cast is used to explicitly convert the boolean value to an integer.

3. Handling Errors and Exception Cases

When using the CAST() function in PostgreSQL to convert data types, it is important to handle errors and exception cases to ensure the desired behavior. Here are a few approaches for handling errors and exceptions with CAST():

3.1 Using a TRY…EXCEPT block

In this approach, you can use a TRY…EXCEPT block to catch and handle any exceptions raised during the cast. Inside the EXCEPTION block, you can handle the error case by raising a notice or performing alternative actions like providing a default value.

begin
-- Attempt the cast
select
cast('abc' as INTEGER);

exception
-- Handle the exception
when invalid_text_representation then
-- Handle the error case
raise notice 'Invalid text representation';
-- Provide a default value or perform an alternative action
end;

3.2 Using a CASE statement

Here, you can use a CASE statement to conditionally perform the cast based on certain criteria. In this example, the cast is attempted only if the string ‘abc’ matches the regular expression ‘^\d+$’, which represents a sequence of digits. Otherwise, you can provide a default value or NULL as needed.

3.3 Using a Combination of Functions and Conditions

This approach involves using a combination of functions like COALESCE(), NULLIF(), and the cast itself. Here, the cast is attempted, and if it results in ‘NaN’, indicating a failed conversion, NULLIF() replaces it with NULL, and then COALESCE() provides a default value of 0.

select
	coalesce(nullif(cast('abc' as INTEGER),
	'NaN'),
	0);

4. Conclusion

In conclusion, the `CAST()` function in PostgreSQL is a powerful tool for converting data from one data type to another. It allows you to explicitly define the desired data type for a value, ensuring proper interpretation and manipulation of data within the database.

Using `CAST()`, you can convert data between various numeric types, string types, date and time types, boolean types, binary data types, network address types, JSON types, UUID types, enumerated types, geometric types, array types, and range types.

The ability to perform data type conversion is essential for maintaining data integrity, enabling effective data analysis and reporting, optimizing query performance, ensuring compatibility and interoperability with different systems, facilitating data validation and error handling, and optimizing storage space.

It’s important to handle potential errors and exception cases when using `CAST()` by implementing appropriate error handling mechanisms, such as `TRY…EXCEPT` blocks or conditional statements, to ensure smooth execution and avoid unexpected errors.

By leveraging the flexibility and versatility of the `CAST()` function, you can manipulate and transform data in PostgreSQL to suit your specific needs and requirements, enhancing the functionality and efficiency of your database operations.

You can download the sql scripts used in this tutorial from the Downloads section.

5. Download the Sql scripts

This was a tutorial to understand the CAST() function in postgresql.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.