Introduction

Cast is a technique in PostgreSQL with which we can convert a value of one datatype into another. We can perform various cast operations in PostgreSQL for converting one datatype to another, such as the String datatype to the Integer datatype (or the Boolean datatype or datetime datatype) and vice versa. This article will illustrate the functions of CAST operator in PostgreSQL with hands-on examples.

Why do we need to CAST?

Let's understand why do we need the casting technique (or briefly the CAST operator) in PostgreSQL with a very simple yet practical example. Suppose we need to add 2 numbers stored in 2 columns of a row/record in a table. However, though the column stores numbers/integers, the datatype has been set as varchar by the database administrator. In such case, the number/integer from such column will be returned as a varchar (or string). So, to add the numbers, we would need to cast them to integer once. This can be done using the CAST operator.

The syntax for using the CAST operator is:

CAST ( expression AS target_type );

where:

1. The expression can be a constant or a table column or any expression that finally resolves to a value,
2. The target_type is the final datatype to which you want to convert the above expression to.

Instead of using the above syntax, we can also use the following condensed syntax:

expression::type

which serves exactly the same purpose.

Suppose we want the number 97 to be returned to us a String datatype. This means we need to convert an integer datatype to string datatype. We can do it either writing:

SELECT CAST(97 AS varchar);

or

SELECT 97::varchar;

and both of the returns a result set as:

Different scenarios of CAST in PostgreSQL

Just like other operations, CAST in PostgreSQL also has it's own DOs and DONTs. To use the CAST operator to type cast from datatype to another, we also need to ensure that the data can be converted to the target datatype or qualifies to be of the target datatype too. We will understand few such DOs and DONTs with some simple examples.

Scenario 1: Casting a String to Integer

Like we discussed above, suppose we need to do some mathematical calculations on a number, which is actually stored in a varchar datatype column. If we write something like:

SELECT '100'::integer;

we get a proper result set where 100 is returned as an integer and not a string.

But if we want to cast an alphanumeric text, say "100ab" to integer, which is not possible. We write this:

SELECT '100ab'::integer;

and immediately we get an error as:

The reason is that 100ab comprises of both numbers and characters. So when we specify it to be cast to integer, the "to be casted" object should comprise of numbers only. So if we drop the character part from the above example, it will work fine. Always note that the value being casted should be qualified to be casted to the resultant datatype.

Scenario 2: Casting a String to Date datatype

Suppose we need to do some date calculations like estimating the age of an employee. In such case, consider we have a date of birth of an employee in a string format (say in MM-DD-YYYY format) and we want to convert it to a date datatype. We can write something like:

SELECT CAST ('01-01-1990' AS DATE)

This returns the result set as:

The common date formats that can be used in string date are: MM/DD/YYYY, MM-DD-YYYY, MM-DD-YY, MM/DD/YY, YYYY/MM/DD, YYYY-MM-DD.

However, again if we try to CAST any string that is not in any of the standard date formats (for example, 01-0c-1994), the function will return an appropriate error as follows:

The obvious reason for the above cast failure is that the string date was not in any of the permissible formats discussed above.

Scenario 3: Casting a String to Double datatype

We might need to convert a string to double datatype, for which we would expect to write something like:

SELECT '10.25'::DOUBLE

Double is actually not a data type in PostgreSQL and hence it fails above. Instead, we have to use double precision to make it work. This code:

SELECT '10.25'::DOUBLE PRECISION

which will return a result set as:

Scenario 4: Casting a String to Boolean datatype

If we need to convert a string datatype to Boolean datatype, then we can use any of the following syntaxes:

SELECT CAST('true' AS BOOLEAN),
CAST('false' as BOOLEAN),
CAST('T' as BOOLEAN),
CAST('F' as BOOLEAN),
CAST('1' as BOOLEAN),
CAST('0' as BOOLEAN);

which returns the result set as:

However, if we try to convert the value 3 to Boolean datatype, it will fail with the error:

Boolean values are represented as 1 for True and 0 for False. Also in short hand, T for True and F for false. However, any number other than 0 and 1 means nothing from Boolean perspective and hence 3 cannot be converted to Boolean datatype.

Scenario 5: Converting a String to Timestamp datatype

To convert a string to timestamp datatype, we can do it writing:

SELECT '02-12-2020 13:13:21'::timestamp;

which will return the result set as:

Scenario 6: Converting a String to Interval datatype

Suppose we need to report the interval elapse for the user to complete every step of an entire operation. In such case, we would need the time elapsed in interval so that we can do date time operations easily using PostgreSQL internal in-built functions.

To convert a string to interval datatype, we can use any of the following syntax or similar:

SELECT '55 minute'::interval,
'2.5 hour'::interval,
'1.5 day'::interval,
'2.5 week'::interval,
'3.2 month'::interval;

which returns the result set as:

So any string value for time elapsed/spent (in hours, seconds, days, weeks, months, etc.) is easily convertible to interval data type which can later be used for some date time operations.

Scenario 6: Casting datatype of rows in a table

Here we will discuss about a practical scenario where a table column stores mixed data, say both numbers and characters. For example, assume that we have a student table where the marks column can store both grades (alphabets) and numbers (numeric). Let's create the table first using the script:

CREATE TABLE student(
roll_number serial PRIMARY KEY,
marks VARCHAR (3) NOT NULL
);

Now let's insert grades for 3 students as:

INSERT INTO student (marks)
VALUES ('A'), ('B+'), ('A-');

Now let's insert numbers for next 3 students as:

INSERT INTO student (marks)
VALUES (52), (89), (98);

So if we see the contents of the student table, we see something similar to:

Now suppose we need to display the marks of all the students in numbers only. For this, if we remember, earlier we discussed that only numbers can be casted into integer but not alphabets. So, only those roll numbers having numeric values in marks will be shown as numbers after casting to integer. The roll numbers having grades in marks (like A, B+) will be shown as 0.

Note: Ideally if we try to cast alphabets to integer, it fails and gives error. But here, they are shown as 0 instead since we're using a CASE and WHEN condition. In the WHEN condition, we're specifying the regex pattern to check if the marks is number, then cast it to integer data type (which will retain the marks as integer datatype), ELSE we're specifying to be displayed as 0. This ELSE condition prevents the cast error from being thrown.

To achieve this, we can write something like:

SELECT
roll_number,
CASE
WHEN marks~E'^\\d+$' THEN
CAST (marks AS INTEGER)
ELSE
0
END as student
FROM
student;

and we get a result set as:

Conclusion

We discussed on the need and benefit of the CAST operator in PostgreSQL and how we can use them in our day-to-day activities to easily inter-covert datatypes. We can also create ENUM types with fixed set of values that are permissible against a table column and use any input value as a CAST against that column. There is much more that we can do with the CAST operator but we always need to keep in mind the bottom line which is that the data should be permissible and allowable against the final datatype for the casting to be successful.