Casting in PostgreSQL

Casting data types in Postgresql

Data conversion is common in all programing paradigms especially when handling transactions in the databases. Like in other databases postgresql database provides for CAST operations which enable conversion of one data type into another. There are various cast operations in postgresql for converting string to integers, casting to Boolean, casting string to date and vice versa. This article illustrates with examples the functions of CAST operator in postgresql. When using this operator the following syntax is utilized:

whereby the expression can either be a constant, a table column, or an expression that evaluates to a specific value. The target_type is the target data type which our expression will result to after conversion. Another version of casting in postgresql involves the use of the :: operator. The syntax in this case is as follows:

Let us now take a few examples using the CAST operator.

String to integer Casting

When converting a string into an integer, we use the following expression

This illustration cast the string type “500” into and integer type 500. One key thing to remember is that a successful conversion occurs when the target data type is compatible with the expression. Using the example above, postgresql would through a type conversion error when we replace the expression “500” with “a500” since they are not compatible.

Postgres cast to Boolean

Casting expressions to Boolean converts them into logical data types. Below is an example that uses CAST ( ) function to cast strings, char and integers expressions into Boolean data types.

Postgresql cast timestamp to date

We obtain exact date from a specific timestamp by using Cast :: operator with the timestamp as input and adding ::date suffix:

The syntax is as follows:

Take an example when you want to extract from ‘2019-07-13 1:20:50′ to ‘2019-07-13’  Our Cast query would be:

Postgresql cast double precision to numeric.

Double precision expression takes more decimal points when compared to float data types. In case of processor memory, the double precision types can occupy up to 64 bit of memory. When converting from double precision, it is quite similar to rounding off the expression. However, casting is used when we want to obtain the accurate result from the expression. In the example below, we will use the cast operator :: together with the Select statement to illustrate the conversion.

Syntax : SELECT (Double precision :: numeric); SELECT (33.135941093 :: numeric);

An alternative way of doing it is to use the cast () function as indicated below:

Syntax :

SELECT CAST (Double precision AS numeric)

Postgresql Cast string to a date

The following example illustrates string casting to date using sql command SELECT:

In the example we first convert the string ‘2019-07-14’ into July 14th, 2019 while in the second part we convert ’13-JULY-2019’ into July 13th, 2019.

Conclusion

We have shown you several techniques of casting along with several examples. We hope you have found one of these examples useful and are able to apply what you’ve learned to your specific application.

If you need help setting up or managing your PostgreSQL database please don’t hesitate to reach out to us at Object Rocket to discuss the details of your project and determine if we can help.