Updated on 2024-09-06 GMT+08:00

User-Defined Data Type Conversion

Description

There are three data type conversion modes for PostgreSQL: implicit conversion, assignment conversion, and explicit conversion. They correspond to i (Implicit), a (Assignment), and e (Explicit) in the pg_cast system catalog.

  • Implicit conversion: a conversion from low bytes to high bytes of the same data type, for example, from int to bigint
  • Assignment conversion: a conversion from high bytes to low bytes of the same data type, for example, from smallint to int
  • Explicit conversion: a conversion between different data types

How to Use

  1. Before converting data types, you can run the following command to check whether RDS for PostgreSQL supports data type conversion:
    select * from pg_catalog.pg_cast ;
    oid  | castsource | casttarget | castfunc | castcontext | castmethod 
    -------+------------+------------+----------+-------------+------------
     11277 |         20 |         21 |      714 | a           | f
     11278 |         20 |         23 |      480 | a           | f
     11279 |         20 |        700 |      652 | i           | f
     11280 |         20 |        701 |      482 | i           | f
    ......
  2. Run the following command to check whether int4 can be converted to text:
    select * from pg_catalog.pg_cast where castsource = 'int4'::regtype and casttarget = 'bool'::regtype;
      oid  | castsource | casttarget | castfunc | castcontext | castmethod 
    -------+------------+------------+----------+-------------+------------
     11311 |         23 |         16 |     2557 | e           | f
    (1 row)

    The conversion is supported, and the conversion type is implicit conversion.

    If no built-in conversion functions are available, customize a conversion function to support the conversion. For details, see User-Defined Data Type Conversion.

User-Defined Data Type Conversion

  • Use double colons (::) to perform a forcible conversion.
    select '10'::int,'2023-10-05'::date;
     int4 |    date    
    ------+------------
       10 | 2023-10-05
    (1 row)
  • Use the CAST function to convert the type.
    select CAST('10' as int),CAST('2023-10-05' as date);
     int4 |    date    
    ------+------------
       10 | 2023-10-05
    (1 row)
  • Customize a data type conversion.

    For details, see https://www.postgresql.org/docs/14/sql-createcast.html.

    Adding a custom type conversion will affect the existing execution plans of RDS for PostgreSQL. Therefore, customizing type conversions are not recommended.

    • Conversion between time and character types

      CREATE CAST(varchar as date) WITH INOUT AS IMPLICIT;

    • Conversion between boolean types and numeric types

      create cast(boolean as numeric) with INOUT AS IMPLICIT;

    • Conversion between numeric types and character types

      create cast(varchar as numeric) with INOUT AS IMPLICIT;

    Example: Convert text to date.

    create or replace function public.text_to_date(text) returns date as 
    $$          
      select to_date($1,'yyyy-mm-dd');  
    $$ 
    language sql strict;  
    
    create cast (text as date) with function public.text_to_date(text) as implicit;  
    
    select text '2023-09-09' + 1;
      ?column?  
    ------------
     2023-09-10
    (1 row)