How to Use the Postgres Cast Function

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
SELECT CAST ( VALUE AS TYPE )

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:

1
SELECT VALUE::TYPE

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:

1
2
3
4
5
SELECT CAST( 5137 AS TEXT );
 text
------
 5137
(1 ROW)

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:

1
2
3
4
5
SELECT CAST( '214' AS INTEGER );
int4
------
  214
(1 ROW)

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:

1
2
3
4
5
SELECT CAST(7419 AS MONEY);
   money
-----------
 $7,419.00
(1 ROW)

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:

1
2
3
4
5
SELECT CAST ('1 year 2 months 7 days' AS INTERVAL);
       INTERVAL
----------------------
 1 YEAR 2 mons 7 days
(1 ROW)

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:

1
2
3
4
5
SELECT CAST( 'July 24, 1999' AS DATE );
    DATE
------------
 1999-07-24
(1 ROW)

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

1
2
3
4
5
SELECT CAST( '24-07-99' AS DATE );
    DATE
------------
 1999-07-24
(1 ROW)

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:

1
2
3
4
5
SELECT CAST( TRIM( LEADING 'DATE ' FROM 'DATE January 28, 1998' ) AS DATE);
   ltrim
------------
 1998-01-28
(1 ROW)

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:

1
2
3
4
5
SELECT 'The current date is ' || CAST( CURRENT_DATE AS TEXT );
            ?COLUMN?
--------------------------------
 The CURRENT DATE IS 2020-03-28
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.