While working with PostgreSQL, we may encounter a situation where we need to convert one data type into another. For instance, converting a numeric string into an int, a string to date, etc. For this purpose, PostgreSQL provides a CAST operator that assists us in converting one data type to another.
Using practical examples, this post will explain how the CAST operator works in PostgreSQL. So, let’s begin.
How to Use the CAST Operator in PostgreSQL?
The below snippet illustrates the basic syntax of the CAST operator:
CAST (exp AS data_type);
Here, exp represents an expression to be evaluated, such as a table’s column, an expression, or a constant. While data_type represents a targeted data type. The given expression will be converted into the targeted data type.
Example #1: How to Use CAST Operator to Convert/Cast a String to Integer?
Run the below statement to convert the given constant string to an integer:
SELECT CAST ('572' AS INTEGER);
The output proves that the CAST operator takes a constant string and converts it into the desired data type, i.e., integer.
Example #2: How to Use the CAST Operator to Table’s Column?
We have created a table named team_details. Let’s run the SELECT statement to get the table’s details:
SELECT * FROM team_details;
The result set shows that the team_rating column has a TEXT data type. Let’s convert it into INTEGER data type using the CAST operator:
SELECT CAST(team_rating AS INTEGER) FROM team_details;
The output shows that the data type of team_rating column has been changed to INTEGER data type.
Example #3: How to Use CAST Operator For String to Date Type Conversion?
We have created a table named article_details in our database. Let’s execute the SELECT query to get the table’s details:
SELECT * FROM article_details;
The published_date column has a text data type. Let’s convert it into DATE type using the CAST operator:
SELECT CAST(published_date AS DATE) FROM article_details;
The data type of published_date column has been converted to the DATE type.
Example #4: How to Use a CAST Operator to Convert a String Into a Double Type?
In our example database, we have created a bike_details table that contains following records:
SELECT * FROM bike_details;
The bike_price column has a TEXT data type. Let’s execute the below statement to convert the TEXT data type to DOUBLE PRECISION data type:
SELECT CAST(bike_price AS DOUBLE PRECISION) FROM bike_details;
The selected column has been converted into the desired data type i.e. DOUBLE PRECISION.
Example #5: Invalid Type Conversion in Postgres?
Trying to convert an expression that cannot be converted to the specified data type will generate an error. Let’s consider the same bike_details table:
SELECT * FROM bike_details;
This time we will try to convert the bike_number column into the DOUBLE PRECISION data type:
SELECT CAST(bike_number AS DOUBLE PRECISION) FROM bike_details;
You can see that Postgres generates an error “invalid input syntax”. This is because the bike_number column contains alphanumeric values that cannot be converted into the DOUBLE PRECISION data type.
This is how the CAST Operator works in PostgreSQL.
Conclusion
PostgreSQL provides a CAST operator that assists us in converting one data type to another. For instance, you can convert a numeric string into an integer, string to double precision, string to boolean, etc. The CAST() operator takes an expression/column and a data type. Consequently, it converts the given expression into the specified data type. In this post we considered several examples to explain the working of the CAST() operator.
