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

- 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".