This topic lists the rules for implicit conversions of data types in Apsara PolarDB-O.

Figure 1. Table of implicit conversion rules
Table of implicit conversion rules
  • NA: indicates that implicit type conversion is not supported. Example:
        explain verbose select CAST(c1 as timestamp) from t_smallint;
        ERROR:  cannot cast type smallint to timestamp without time zone
  • e: indicates that explicit type conversion is supported only by the CAST or :: syntax. Example:
        create table t_int(c1 integer);
        insert into t_int values(2);
        select CAST(c1 as boolean) from t_int;
         c1
        ----
         t
        insert into t_int values('true'::boolean);
        ERROR:  column "c1" is of type integer but expression is of type boolean
  • 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 INTEGER)) FROM DUAL;   -- fail
        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
Note

The actual type of DATE data stored in an Apsara PolarDB-O database is determined by the parameter polar_comp_redwood_date:

  • polar_comp_redwood_date=on (default): The data type is TIMESTAMP WITHOUT TIME ZONE, which is compatible with Oracle mode. For more information about the implicit type conversion rules, see Table of implicit conversion rules.
  • polar_comp_redwood_date=off: The data type is DATE, which is compatible with PostgreSQL mode. The following shows some of the implicit type conversion rules. For more information, see Table of implicit conversion rules.
    • When the type of the source data is DATE and the type of the target data is TIME WITHOUT TIME ZONE, the implicit conversion rule is "NA".
    • When the type of the source data is TIMESTAMP WITHOUT TIME ZONE and the type of the target data is DATE, the implicit conversion rule is "a".
    • When the type of the source data is TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) and the type of the target data is DATE, the implicit conversion rule is "a".
    • When the type of the source data is TIMESTAMP WITHOUT TIME ZONE (TIMESTAMP) and the type of the target data is DATE, the implicit conversion rule is "a".