This topic lists the rules for implicit conversions of data types in Apsara PolarDB for PostgreSQL(Compatible with Oracle).
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