Converting Data Types in PostgreSQL (Using CAST and CONVERT Functions)

1. Introduction

Data type conversion refers to the process of converting a field's value into another data type. In PostgreSQL, this can be achieved using CAST and CONVERT functions.

2. CAST Function

The CAST function is used to forcibly convert a value to a specified data type based on ASCII tables.

CAST('123' AS INTEGER) --> 123

3. CONVERT Function

The CONVERT function also converts data types, but it uses built-in programming language conversion rules rather than the ASCII table. This conversion is more flexible and suitable for scenarios requiring complex conversions.

CONVERT(REAL FROM '123') --> 123.0

4. Conversion of Different Data Types

1. date, timestamp, interval

  • CAST('2022-01-01' AS DATE)
  • CAST('2022-01-01 12:00:00' AS TIMESTAMP)

2. time 

CAST('14:30:00' AS TIME)

3. numeric, integer 

CAST('123' AS NUMERIC) --> 123.0000

4. boolean

  • CAST(true AS BOOLEAN) --> TRUE
  • CAST(false AS BOOLEAN) --> FALSE

5. Special Cases

1. NULL conversion 

CAST('NULL' AS INTEGER) --> NULL

2. None type conversion

PostgreSQL does not have a corresponding type for this; typically, the Cast or Convert functions are used to implement mutual conversions.

6. Example

In some scenarios, you may encounter issues such as:

  • Some functions only accept specific data types.
  • For various reasons, it is necessary to convert one column of data into another data type.

For example, if we want to use the NOW() function to get the current time, but the NOW() function requires parameters in the form of TIMESTAMP, we can use CAST or CONVERT functions for conversion: 

CAST(CURRENT_TIMESTAMP AS TIMESTAMP)

7. Summary

In PostgreSQL, data type conversions are performed using CAST and CONVERT functions. The CAST function primarily performs conversions based on ASCII tables, while the CONVERT function is based on programming language conversion rules. Both functions have their own characteristics and application scenarios.