Type Conversion Functions

CAST

CAST(expr AS type)

Description: Explicitly converts expr to type, which is equivalent to CONVERT(expr, type). type supports the following data types:

• BINARY[ (N) ]

  If expr is NULL, the return value type is BINARY(0). If expr is not NULL, the return value type is VARBINARY. If the optional length N is specified, the part that exceeds N is truncated, and the part that is less than N is padded with 0x00. If the optional length N is not specified, the length of the return value is determined based on the result of the expression. If the length of the return value exceeds the upper limit of BINARY, the return value type is BLOB. If the length is still insufficient, the return value type is LONGBLOB. The value of N is affected by the GUC parameter max_allowed_packet. The range of the maximum value is Min(max_allowed_packet, 1073741819).

• CHAR[ (N) ]

  If expr is NULL, the return value type is CHAR(0). If expr is not NULL, the return value type is VARCHAR. If the optional length N is specified, the part that exceeds N is truncated, and the part that is less than N is not padded. If the optional length N is not specified, the length of the return value is determined based on the result of the expression. If the length of the return value exceeds the upper limit of VARCHAR, the return value type is TEXT. If the length is still insufficient, the return value type is LONGTEXT.

• DATE

  The return value type is DATE.

• DATETIME[ (M) ]

  The return value type is DATETIME. M is used to determine the precision of seconds. The value range is [0,6].

• TIME[ (N) ]

  The return value type is TIME. M is used to determine the precision of seconds. The value range is [0,6].

• DECIMAL[ M [ , D ] ) ]

  The return value type is DECIMAL. M is used to determine the maximum number of digits (precision). The maximum value is 65, and the default value is 10. D is used to determine the number of digits after the decimal point. The maximum value is 30, and the default value is 0.

• DOUBLE

  The return value type is DOUBLE.

• FLOAT[ (p) ]

  If the precision p is not specified, the return type is FLOAT. If the precision p is greater than or equal to 0 and is less than 24, the return value type is FLOAT. If the precision p is greater than or equal to 24, the return value type is DOUBLE. If the precision p is less than 0, an error is returned.

• JSON

  The return value type is JSON.

• SIGNED[ INTEGER ]

  The return value type is signed BIGINT.

• UNSIGNED[ INTEGER ]

  The return value type is BIGINT without signs.

Examples:

m_db=# SELECT CAST('abc' AS BINARY(2));
WARNING:  Truncated incorrect binary(2) value: 'abc'
CONTEXT:  referenced column: cast
 cast 
------
 ab
(1 row)

m_db=# SELECT CAST('abc' AS CHAR(10));
 cast 
------
 abc
(1 row)

m_db=# SELECT CAST('2023/1/1' AS DATE);
    cast    
------------
 2023-01-01
(1 row)

m_db=# SELECT CAST('2023/01/01' AS DATETIME(2));
          cast          
------------------------
 2023-01-01 00:00:00.00
(1 row)

m_db=#  SELECT CAST('09:23:14' AS TIME(2));
    cast     
-------------
 09:23:14.00
(1 row)

m_db=# SELECT CAST(12.34567 AS DECIMAL(10,4));
  cast   
---------
 12.3457
(1 row)

m_db=# SELECT CAST(-12 AS SIGNED);
 cast 
------
  -12
(1 row)

m_db=# SELECT CAST(-12 AS UNSIGNED);
         cast         
----------------------
 18446744073709551604
(1 row)

CONVERT

• CONVERT(expr, type)

  Description: Explicitly converts expr to type, which is equivalent to CAST(expr, type).

  Examples:

  m_db=# SELECT CONVERT('abc', BINARY(2));
  WARNING:  Truncated incorrect binary(2) value: 'abc'
  CONTEXT:  referenced column: convert
   convert 
  ---------
   ab
  (1 row)
  
  m_db=# SELECT CONVERT('abc', CHAR(10));
   convert 
  ---------
   abc
  (1 row)
  
  m_db=# SELECT CONVERT('2023/1/1', DATE);
    convert   
  ------------
   2023-01-01
  (1 row)
  
  m_db=# SELECT CONVERT('2023/01/01', DATETIME(2));
          convert         
  ------------------------
   2023-01-01 00:00:00.00
  (1 row)
  
  m_db=# SELECT CONVERT('09:23:14', TIME(2));
     convert   
  -------------
   09:23:14.00
  (1 row)
  
  m_db=# SELECT CONVERT(12.34567, DECIMAL(10,4));
   convert 
  ---------
   12.3457
  (1 row)
  
  m_db=# SELECT CONVERT(-12, SIGNED);
   convert 
  ---------
       -12
  (1 row)
  
  m_db=# SELECT CONVERT(-12, UNSIGNED);
         convert        
  ----------------------
   18446744073709551604
  (1 row)

• CONVERT(expr USING transcoding_name)

  Description: Converts the expression expr to the character set specified by transcoding_name.

  Examples:

  m_db=# SELECT CONVERT('abc' USING 'latin1');
   convert 
  ---------
   abc
  (1 row)