# PostgreSQL CAST Operator- How to Convert One Data Type to Another

> PostgreSQL offers a CAST operator that takes an expression and a data type and converts the given expression into the specified data type.

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-cast-operator-how-to-convert-one-data-type-to-another/)