PostgreSQL Cast Function 101: How to Convert One Data Type to Another?

PostgreSQL is a powerful open-source object-relational database system. Its proven design and more than 30 years of active development have given it a solid reputation for dependability, data integrity, and correctness. 

In various situations, you may need to change a value from one data type to another. The PostgreSQL CAST operator enables you to accomplish this. For example, we can convert a string data type to an integer data type and vice versa.

This article will help you develop a holistic understanding of PostgreSQL and its key features and will further demonstrate the creation, use & critical aspects of the CAST operator.

What is PostgreSQL?

PostgreSQL is an open-source, high-performance relational database renowned for having a platform that supports all RDBMS functionality. It provides indexes, views, stored procedures, triggers, atomicity features, etc. in addition to RDBMS features. Additionally, it supports SQL and JSON queries. PostgreSQL also offers to build a data warehouse system.

Numerous operating systems, such as Windows, Linux, macOS, UNIX, etc., are supported by PostgreSQL. The source code for PostgreSQL is freely accessible under an open-source license, enabling users to use, modify, and adapt it per their needs. There is no risk of over-deployment with PostgreSQL because there is no license fee.

Key Features of PostgreSQL

• Customizable: To make PostgreSQL match your needs, plugins can be made to alter the DBMS. Additionally, it enables the inclusion of unique functions created in various other programming languages, including Java, C, C++, and others.
• Supportive Community: For its users, a committed community is always available. PostgreSQL is compatible with a wide range of private, third-party support services.
• Open-Source: PostgreSQL offers Object-Oriented and Relational Database capabilities and is a free open-source database system.
• Users: It is a well-liked and utilized RDBMS. Apple, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, and more companies use PostgreSQL. 
• Code quality: The whole development process for PostgreSQL is community-driven, allowing for speedy bug reporting, fixes, and verification. Every line of code added to PostgreSQL is reviewed by a team of professionals.
• Data Availability and Resiliency: For mission-critical production environments like those found in governmental bodies, financial institutions, and healthcare providers, privately supported PostgreSQL versions provide extra high availability, resilience, and security.

Also, take a look at the PostgreSQL Array Functions to get a better understanding of the various operations that can be performed on PostgreSQL.

What is PostgreSQL Cast Function?

The PostgreSQL CAST operator can be used to change the type of a value. In PostgreSQL, there are two ways to CAST a value. 

• In the first approach, the PostgreSQL CAST function’s parentheses are used to define the value and the desired data type. The syntax is demonstrated below:

SELECT CAST ( VALUE AS TYPE );

Here, start by defining an expression that can be a table column, a constant, or an expression that produces a value. Next, select the target data type to which the expression’s result will be converted.

• Adding the :: notation between a value and the desired data type is another approach to cast a value. The syntax is displayed below:

SELECT VALUE::TYPE

The value you want to cast or modify the data type for is represented by the value in this syntax. The type specifies the data you want to use for this value; examples of type values include INTEGER, FLOAT, and CHAR. Read along to learn more about the Postgres Cast Function’s Use cases.

What is the need for the PostgreSQL Cast Function?

While working with the data that is stored in PostgreSQL, one could encounter a situation where you would prefer to treat data as a different data type. For instance, if the “48” which represents a number, is stored as a text. It might be more logical to convert this value to an integer to perform computations on it. Here comes the PostgreSQL CAST function in rescue, which is used to change a value’s data type to another data type. If the value is not appropriate for the intended data type.

Learn how to effectively use the PostgreSQL VARCHAR data type for flexible string storage in your databases. Discover more at PostgreSQL VARCHAR.

PostgreSQL Cast: Example Use Cases

• PostgreSQL Cast String to Integer
• PostgreSQL Cast to String
• PostgreSQL Cast String to Interval
• PostgreSQL Cast String to Date
• PostgreSQL Cast With Trim
• PostgreSQL Cast With Concatenation
• PostgreSQL Cast String to Boolean
• PostgreSQL Cast a String to Double 
• PostgreSQL Cast String to Timestamp 

PostgreSQL Cast String to Integer

SELECT CAST( '200' AS INTEGER );
------OUTPUT------
  200
(1 ROW)

Here, this statement casts a string constant to an integer.

Note: If the expression fails to execute. The reason for this error is that 20D consists of both numbers and characters. So when we cast it to an integer, the input string should comprise numbers only. For example:

PostgreSQL CAST to String

Here, this statement casts a value as a string value.

SELECT CAST( 800 AS TEXT );
------OUTPUT------
 800
(1 ROW)

Let’s take another example, here we’ll cast an integer value to the MONEY data type. 

SELECT CAST(2400 AS MONEY);
------OUTPUT------
 $2,400.00
(1 ROW)

PostgreSQL Cast String to Interval

Consider the scenario where we must report the time that has passed since the user completed each stage of the entire transaction. In this scenario, we would require the interval time to do date and time operations with PostgreSQL’s internal built-in functions quickly.

Any of the following methods can transform a string into an interval datatype. Therefore, it is simple to convert any text value for the time that has passed or been spent (in hours, seconds, days, weeks, months, etc.) into an interval data type that can then be utilized for some date and time operations.

SELECT CAST ('2 year 5 months 3 days' AS INTERVAL);
------OUTPUT------
 2 YEAR 5 mons 3 days
(1 ROW)

PostgreSQL CAST String to Date

Here, we can use the PostgreSQL CAST function for text value to a DATE data type, as seen in the example below:

SELECT CAST( 'October 18, 1998' AS DATE );
------OUTPUT------
 1998-10-18
(1 ROW)

There’s another way to CAST text as a date:

SELECT CAST( '18-10-98' AS DATE );
------OUTPUT------
  1998-10-18
(1 ROW)

PostgreSQL CAST With Trim

Here, we’ll trim a text value & then cast the result that’s obtained from TRIM:

SELECT CAST( TRIM( LEADING 'DATE ' FROM 'DATE October 18, 1998' ) AS DATE);
------OUTPUT------
 1998-10-18
(1 ROW)

PostgreSQL CAST With Concatenation

Here, we’ll concatenate a string value with a date value cast to TEXT. We can also concatenate two or more strings using the || operator. Let’s see how it’s done:

SELECT 'The current date is ' || CAST( CURRENT_DATE AS TEXT );
------OUTPUT------
 The CURRENT DATE IS 2022-07-05
(1 ROW)

Note: String dates can be written in the following standard date formats: MM/DD/YYYY, MM-DD-YYYY, MM-DD-YY, MM/DD/YY, YYYY/MM/DD, and YYYY-MM-DD.
However, once again, the method will return the appropriate error if we attempt to CAST any string, not in one of the accepted date formats (for instance, 01-0c-1994).

PostgreSQL Cast String to Boolean

In this example, we’ll use the CAST operator to convert the string ‘true’, ‘T’ to true and ‘false’, ‘F’ to false:

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

Note: 1 stand for True and 0 for False in a boolean value. T stands for true and F for false in shorthand. 3 cannot be transformed to a Boolean data type because, from a Boolean perspective, any number other than 0 and 1 means nothing.

PostgreSQL Cast a String to Double 

Here we need to use Cast to convert a string to double datatype.

SELECT '15.75'::DOUBLE

This will generate an error message because Double is not a data type in PostgreSQL. Instead, we have to use double precision. For example:

SELECT '15.75'::DOUBLE PRECISION

PostgreSQL Cast String to Timestamp 

To convert a string to timestamp datatype, proceed as follows:

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

Conclusion

In this article, we have thoroughly learned about PostgreSQL and its key features. We also discussed the necessity of the CAST operator and how we may use it in regular operations to switch between different data types quickly.

Master string manipulation in PostgreSQL using the TRIM function to streamline your queries. Explore its features in PostgreSQL TRIM. Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions