Apsara PolarDB for PostgreSQL (Compatible with Oracle) supports four levels of implicit type conversion between data types. Understanding these levels helps you write portable SQL, diagnose type mismatch errors, and avoid unintended performance impacts from silent coercion.
Figure 1. Table of implicit conversion rules
Notation legend
Each cell in the matrix uses one of four symbols. The symbols are cumulative: each level includes the capabilities of all lower levels.
NA — No conversion
Implicit type conversion is not supported between the two types. Attempting a conversion raises an error.
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 — Explicit conversion only
Conversion is supported only through an explicit CAST(x AS type) or x::type expression. The database does not convert types automatically in assignments or expressions.
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 — Assignment conversion
In addition to explicit CAST and ::, the database implicitly converts a value when assigning it to a target column via INSERT VALUES or UPDATE SET.
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); -- oki — Full implicit conversion
In addition to e and a, conversion is applied automatically in any context, including function and expression parameters.
-- 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