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