All Products
Search
Document Center

PolarDB:Implicit conversion rules

Last Updated:Sep 08, 2023

This topic lists the rules for implicit conversions of data types in Apsara PolarDB for PostgreSQL(Compatible with Oracle).

Figure 1. Table of implicit conversion rulesimage.png

  • NA: indicates that implicit type conversion is not supported. Example:

    create table t_smallint(c1 smallint);
    insert into t_smallint select 1;
    select * from t_smallint ;
     c1
    ----
      1
    (1 row)
    
    explain verbose select CAST(c1 as timestamp) from t_smallint;
    ERROR:  cannot cast type smallint to timestamp without time zone
    LINE 1: explain verbose select CAST(c1 as timestamp) from t_smallint...
  • e: indicates that explicit type conversion is supported only by the CAST or :: syntax. Example:

    create table t_timestamp_without_time_zone(c1 timestamp without time zone);
    insert into t_timestamp_without_time_zone values('2021-10-31 08:00:00');
    select CAST(c1 as time without time zone) from t_timestamp_without_time_zone;
        c1
    ----------
     08:00:00
    (1 row)
    
    insert into t_timestamp_without_time_zone values('08:00:00'::time without time zone);
    ERROR:  column "c1" is of type timestamp without time zone but expression is of type time without time zone
    LINE 1: insert into t_timestamp_without_time_zone values('08:00:00':...
                                                             ^
    HINT:  You will need to rewrite or cast the expression.
  • a: indicates that in addition to e, values can be implicitly assigned to the target column (which assigns values by using INSERT VALUES or UPDATE SET). Example:

        create table t_int(c1 integer);
        insert into t_int values(2);
        select cast(c1 as smallint) from t_int;  -- ok
         c1
        ----
          2
        insert into t_int values(3::smallint);  -- ok
  • i: indicates that in addition to a and e, other implicit conversions are supported, such as expression parameters. Example:

        -- case 1
        CREATE OR REPLACE FUNCTION F_VARCHAR(arg1 VarChar) RETURN void
        IS
        BEGIN
           dbms_output.put_line(arg1);
           RETURN;
        END;
    
        SELECT F_VARCHAR(cast('10' as CHAR(10))) FROM DUAL;  -- ok
    
    -- case 2
        create table t_varchar(c1 varchar(10));
        insert into t_varchar values(2);
        explain verbose select sum(c1) from t_varchar;
                                    QUERY PLAN
        ---------------------------------------------------------------------------
        Aggregate  (cost=43.95..43.96 rows=1 width=32)
          Output: sum((c1)::numeric)
          ->  Seq Scan on public.t_varchar  (cost=0.00..29.40 rows=1940 width=14)
               Output: c1