How to Do Type Casting in PostgreSQL

Type casting allows you to convert a value from one data type to another in PostgreSQL. This is useful when working with mixed data, performing calculations, or ensuring compatibility across queries.

1. Using the CAST function

The most explicit way to cast types in PostgreSQL is with the CAST function:

SELECT CAST('123' AS INTEGER);

This will convert the string '123' into an integer.

2. Using the double colon (::) shorthand

PostgreSQL also allows a more concise syntax using the :: operator:

SELECT '123'::INTEGER;

This produces the same result as CAST() and is widely used in PostgreSQL queries.

3. Casting in table queries

You can cast columns in your queries, for example:

SELECT price::NUMERIC FROM products;

This ensures the price column is treated as a numeric value.

4. Common use cases

• Converting strings to numbers for calculations
• Converting timestamps to dates
• Casting JSON values to text or arrays

5. Tips and pitfalls

To avoid errors:

• Validate data before casting
• Use NULLIF() or COALESCE() to handle unexpected values

6. Example with COALESCE

SELECT COALESCE(NULLIF(column, ''), '0')::INTEGER FROM my_table;

7. Summary

PostgreSQL provides powerful type casting tools that make your queries flexible and robust. Use CAST() or :: as needed, but always check your data to prevent unexpected errors.