When you want to change the value of one data type to another on PostgreSQL, you should use the PostgreSQL CAST option. Maybe you want to achieve a given agenda, but the current data type in your table doesn’t support it. Using the CAST feature allows you to convert the data types easily.

We will discuss how to convert the data types using CAST in PostgreSQL. This post shows the examples of implementing the CAST feature when working with various data types. Take a look!

Examples of Converting the Data Types Using CAST in PostgreSQL

Different situations require changing the data type when working with PostgreSQL. The basic syntax to convert the data types using CAST in PostgreSQL is as follows:

Let’s have examples to understand how casting in PostgreSQL works.

Example 1: Convert Double to Integer

Let’s say you have a double data type but you want to utilize it as an integer. You must use the CAST option to change the data type. The following example uses CAST to convert the value. Notice how the output meets the expected criteria of an integer:

Example 2: Convert the String to Integer

Sometimes, you could have a string value that you want to get its output as an integer. Just specify the string and the desired data type. Once you execute the command, you will have your output as expected:

Example 3: Convert the Integer to Money Data Type

In PostgreSQL, you can convert a given data type to get the money data type. The converted output takes the currency based on your region. Let’s have an example of converting 1400 to money data type and see how its output will be:

Example 4: Convert to Date

Date is a data type. When you have a value that is a string, you can cast it to convert it to the date data type. Here, we type the target string and specify that we want to convert it to date. Look at what output we get:

You can type the target string in a different format, as in the following case, and you will still manage to convert it to a date value as follows:

Example 5: Convert to Text

PostgreSQL has the text data type. You can cast an integer to use it as a text in your output. Here’s a simple example to cast an integer to text.

Example 6: Cast with Concatenation

Let’s say you want to concatenate your output while converting the data type. It’s possible to use the “||” symbol for concatenation. The following example concatenates a statement and casts the current date as a text:

Example 7: Cast to Interval

The interval data type is handy when you want to work with the time and date option in PostgreSQL. As shown in the following illustration, you can cast a string to get the interval by specifying it in your command:

Example 8: String to Double

Earlier, we learned how you can get a string from a double. To get a double from a string, the process slightly changes. Here, we must use the double columns instead of the “cast” keyword. When PostgreSQL reads the command, it automatically detects that you want to cast the string to double.

Example 9: String to Timestamp

It’s also possible to get a timestamp from a string data type. Type the string and set the cast data type as timestamp. The conversion occurs once you press the enter key.

Note: If you try to cast a data type that doesn’t convert to the desired one, you will get an error. For instance, you can’t convert an integer to a date. See what error we get when we try to perform such an operation.

Example 10: Casting from a Table

So far, we manually added the data that we want to convert. However, your data will be in a table in a production environment, and you want to cast it. Select the columns that you want to extract and use the CAST keyword on the target column while specifying the target data type. The following example casts the “points” column to the money data type from the “linux” table:

Conclusion

The cast feature makes it easy to convert the data types in PostgreSQL. This post has given numerous examples of implementing the PostgreSQL cast feature to convert different data types. Hopefully, the examples helped you to understand how converting the data types in PostgreSQL works.