Casting in PostgreSQL: Handling Data Type Conversions Effectively

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.

---

Tools used in this tutorial

DbVisualizer - top rated database management tool and SQL client.

The PostgreSQL database.

---

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:

• (A) Using the CAST() Function

1    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.

• (B) Using the :: Operator

1    expression::target_data_type

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:

1    SELECTCAST(salary AS INTEGER) AS salary_int FROM employees;

Here, the 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

Pros

• Flexibility: PostgreSQL provides a wide range of casting functions, which gives developers a lot of flexibility to convert data between different formats.

• Query Optimization: Casting can also be used in the optimization of queries by converting data types to suit the expected data types of indexes or functions used in the query.

Cons

• Data Loss: Casting can lead to data loss if the conversion is not done correctly. For example, converting a floating-point number to an integer will truncate the decimal portion of the number, resulting in data loss.

• Performance: Casting can come with a performance downside if it's used extensively within your queries or if your database is not optimized for high performance.

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:

Casting a String to an Integer.

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.

Successful Conversion of String to Integer Data Type.

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:

DOUBLE Data Type Error in PostgreSQL.

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:

Rectification of Error with the DOUBLE PRECISION Data Type.

(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:

1    SELECT order_id, CAST(order_date AS DATE), total_cost
2    FROM orders;

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:

Creating and Inserting Data into a Table.

Because the requirements change, let us use the same players table to store ratings as numbers e.g., 1, 2, 3 instead of A, B, and C so that players table stores mixed values including numeric and string data types with the following query:

1    INSERT INTO players (rating)
2    VALUES
3        (1),
4        (2),
5        (3);

To know if our modifications have worked, let us run this query in the SQL commander of DbVisualizer:

Running Query in 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 A, B, and 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.

Converting Values in the ‘Rating’ Column into Integers.

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.

Conclusion

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!

FAQs

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.

About the author

Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn