How to Use the Postgres Cast Function

Introduction

When you work with data stored in PostgreSQL, you might have some data that you would prefer to treat as a different data type. For example, you may have a text value that represents a number, like “53”. In order to perform calculations on that value, it might make more sense to convert that value to an integer. In Postgres, the CAST function is used to convert the data type of a value to a different data type. The value must be compatible with the targeted data type; otherwise, an error will be returned. In this PostgreSQL article, we’ll show you how to use the PostgreSQL CAST function and review some examples of different conversions.

Prerequisites

Before proceeding with this tutorial, two key prerequisites should be in place:

• You must have PostgreSQL installed and configured on your computer.
• You must have some basic knowledge of PostgreSQL.

PostgreSQL CAST Function

There are two ways to CAST a value in PostgreSQL. In the first method, we specify the value and the targeted data type inside the parentheses of the CAST function. The syntax is shown below:

Another way to cast a value is by using the :: notation between the value and the targeted data type. You can see the syntax below:

In this syntax, value represents the value you want to cast or change the data type. The type represents the data type you are targeting for this value; a few possible values of type include INTEGER, FLOAT and CHAR.

Postgres CAST Example

Let’s look at some examples of how to use the Postgres CAST function.

Postgres CAST to String

For our first example, we’ll cast a value as a string value. We can see how this is done in the following SELECT statement:

Postgres CAST to Integer

Next, we’ll convert a value with a text type to an integer. The following statement shows how it’s done:

In the example shown below, we’ll cast an integer value to the MONEY data type. We can see how it works in the following statement:

Postgres CAST to Interval

Our next example will cast a text value to the INTERVAL data type. The following SELECT statement shows how it works:

Postgres CAST to Date

We can also use the CAST function to convert a text value to a DATE data type as seen in the example below:

Here’s another way to CAST text as a date:

Postgres CAST With Trim

In the example shown below, we’ll trim a text value, then we’ll cast the result that’s returned from TRIM:

Postgres CAST With Concatenation

We can concatenate two or more strings using the || operator. In our final example, we’ll concatenate a string value with a date value that is cast to TEXT. The statement below shows how it’s done:

Conclusion

When you query for data in PostgreSQL, you may want the values of certain columns to be converted to a different data type. Changing the data type can make it easier to perform calculations and processing operations on the data that’s returned. In this article, we showed you how to use the Postgres CAST function to convert a value’s data type. If you’ve followed along with all of the examples presented in this article, you’ll be able to use the CAST function effectively in your own PostgreSQL queries.