Implicit conversion rules

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

• 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