Let’s explore the CAST PostgreSQL function, take a look at what it is, what it does, its pros and cons, and when to use it.
Casting types is a crucial aspect of managing data in any application. Without proper data type conversion, you may have unexpected results or even errors that can be hard to detect. That's where the
CAST function in Postgres comes into play!
In this article, you will discover the ins and outs of the
CAST function in Postgres and learn how to use it effectively to convert data types.
What Is CAST in PostgreSQL?
CAST is a PostgreSQL function that defines how to convert data types. In cases where data needs to be converted from one type to another, casting allows you to achieve this seamlessly.
The syntaxes below illustrate the operation of CAST in PostgreSQL:
CAST ( expression AS target_data_type );
In the syntax above, you first specify an expression that can be a constant, a table column, or an expression that evaluates to a value, and then you specify the target data type to which you want to convert the result of the expression.
The :: operator allows you to cast an expression directly to the specified target data type. The expression can be a column name, a literal value, or the result of a calculation. Both syntaxes, however, produce the same results.
How to Use CAST in PostgreSQL
In the general syntax, the "expression" parameter represents the value that you want to convert, and the "data_type" parameter represents the target data type that you want to convert the value to.
For example, let's say you have a table called "employees" with a column called "salary" that contains decimal values. If you want to convert the "salary" values to integers, you can use the
CAST function in a SQL query like this:
SELECT CAST(salary AS INTEGER) AS salary_int FROM employees;
CAST function is used to convert the "salary" values to integers, and the resulting column is named "salary_int" in the result set.
Pros and Cons of CAST
PostgreSQL CASTING: Use Cases
(1) Casting a String to an Integer
Provided that there’s a string ‘069’ that is needed to be converted into an integer, we can use the
CAST function to do this as shown below:
This query will return the integer value 069. The
CAST function takes the string '069' as its input and converts it to an integer data type. Let’s note that the resulting integer value can be stored in an integer column in a table.
It is important to bear in mind that if the expression or input string cannot be converted to the target data type, PostgreSQL will throw an error and so it’s always critical to ensure that the input data, before casting it to the target data type, is validated.
(2) Casting a String to a Double
Let's say we have a string '11.8' that we want to convert to a double. We can use the CAST function to do this as shown here:
You realize that there’s an error. This is because the
DOUBLE data type is not recognized by PostgreSQL as it is not a standard SQL data type and isn’t part of the SQL standard that PostgreSQL follows.
To fix this, you need to use
DOUBLE PRECISION instead of
DOUBLE as shown here in DbVisualizer:
(3) Casting a Table Data
Let's assume we have a table called "orders" with columns "order_id", "order_date", and "total_cost". The "order_date" column is of type
text, and we want to convert it to a
date data type using the
CAST function in a SELECT query as shown below:
SELECT order_id, CAST(order_date AS DATE), total_cost
When the query is executed, the "order_date" column will contain date values instead of text values.
You could also use
CAST with table data in this instance: first, create a table called players that consists of two columns, and then insert some data into the players table with the query below:
Because the requirements change, let us use the same players table to store ratings as numbers e.g.,
3 instead of
C so that players table stores mixed values including numeric and string data types with the following query:
INSERT INTO players (rating)
To know if our modifications have worked, let us run this query in the SQL commander of DbVisualizer:
Great! Everything is on point. Now, let us do this exercise by converting all values in the rating column into integers. When we do this, the
C ratings will be displayed as zero.
To do this, let us build a query that retrieves data from the players table and includes a
CASE statement that checks the value of the rating column and returns a different value if the value is a string that can be converted to an integer or not.
The query above uses a
CASE statement with a regex pattern to conditionally convert the rating column values to integers and returns a default value of 0 for non-numeric values.
In this article, you learned that CAST is a powerful tool for effectively converting data types from one form to another.
To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all PostgreSQL features, including CAST. Download DbVisualizer for free now!
What is PostgreSQL CAST?
PostgreSQL CAST is a function that is used to convert a value of one data type to another data type, such as converting a string to a date.
How do I use PostgreSQL CAST?
To use PostgreSQL
CAST, you can include the
CAST function in your SQL query, followed by the value you want to convert and the target data type enclosed in parentheses.
What should I do if PostgreSQL CAST fails to convert my data?
It may be because the source data isn’t in a format that can be converted to the target data type. When this happens, you may need to transform the data before re-attempting the conversion process.