Using PostgreSQL CAST To Convert From One Data Type Into Another

Summary: in this tutorial, we will show you how to use PostgreSQL CAST to convert from one data type into another.

Introduction to PostgreSQL CAST

There are many cases that you want to convert one data type into another e.g., the requirements change so that you change the data type of a column. PostgreSQL provides the syntax for converting one type into another. The following illustrates the syntax of type CAST:

First, you specify an expression that can be a constant, a table column, etc., that you want to convert. Then, you specify the target type which you want to convert to.

PostgreSQL CAST examples

The following statement converts a string constant into an integer:

If the expression cannot be converted to the target type, PostgreSQL will raise an error. See the following example:

In the following examples, we convert various strings to dates data type:

First, we converted 2015-01-01 literal string into January 1st 2015. Second, we converted 01-OCT-2015 to October 1st 2015.

In the following example, we try to convert a string into a double value:

Whoops, we got the following error message:

To fix this, you need to use DOUBLE PRECISION instead of DOUBLE as follows;

PostgreSQL type cast :: specific

Besides the type CAST syntax, you can use following syntax to convert a type into another:

See the following examples:

Notice that the cast syntax with :: is PostgreSQL specific and does not conform to SQL.

PostgreSQL CAST practical examples

In this section, we show you a practical example of using type CAST.

Suppose, we have a ratings table that has two columns: id and rate. The data type of the rate column is varcharbecause the ratings table stores the ratings as A, B, and C. We can create the ratings table as follows:

We insert some sample data into the ratings table.

Because the requirements change, we use the same ratings table to store numerical ratings.

So the ratings table stores mixed values including numeric and string.

Now, we have to convert all values in the rate column into integers, all other A, B, C ratings will be displayed as zero. To do this, you use the CASE operator with the type CAST as follows:

The CASE operator checks the rate, if it matches the integer pattern, it converts the rate into an integer, otherwise it returns 0.

In this tutorial, we have shown you how to use PostgreSQL CAST to convert a data type into another.