LightDB supports special compatibility between string and numeric types. When implicit casting rules cannot find suitable operators or functions((Section 10.2,Section 10.3)), explicit type casts will be applied automatically and type priority will be applied to select the appropriate operator or function.
This special compatibility feature for 9 types with priority by default. Consider each of these types supports implicit casts to the other.
Here are the default 9 types(in descending order of priority):
numeric > double precision > real > bigint > integer > smallint > text > varchar > char
Your can use ALTER TYPE to modify or add priority of type, built-in type is cannot modify, see ALTER TYPE.
Example 10.15. Alter or add type priority
ALTER TYPE varchar2 set (priority=200)
Type Resolution for Compatibility
Execute the following flow after the rules described in Section 10.2 and Section 10.3.
Ignore all candidates have Pseudo-Types argument.
All unknown-type will be considered as text type.
Run through all candidates and keep those with the least explicit cast on input types. If only one candidate remains, use it; else keep all the remaining candidates and continue to the next step.
Run through all remain candidates, if all the explicit cast positions have the same argument type, the candidate's priority will be the same as the argument type's priority. If candidate's explicit cast positions have more than one argument type, the priority will be lower than all types. In operator, if one argument is number and another is string, the number type's priority will be higher than all types.
Run through all remaining candidates, keep those at the most positions where is exact match. If only one candidate remains, then use it; else continue to the next step.
Run through all remaining candidates, compare candidate's argument type priority at the same position from left to right, keep the candidate that have the highest priority.
Example 10.16. Automatically Apply Explicit Cast
select |/'20'::text;
?column?
------------------
4.47213595499958
(1 row)
In this example, the type text cannot be implicitly cast to double precision,
but by the type compatible rules, explicit cast can be applied automatically between number and string types.
Example 10.17. Type priority
select ~ '20';
?column?
----------
-21
(1 row)
In this example, the input type is unknown-type, so consider the input type as text,
and select the candidate has the highest priority argument,
and use ~ bigint.
If input value cannot convert to bigint, a type convert error will be reported.
select ~ 'a'; ERROR: invalid input syntax for type bigint: "a" LINE 1: select ~ 'a';
Example 10.18. Substring Function Type Resolution
If substr function is called with an argument of type integer,
LightDB will try to convert that to text:
SELECT substr(1234, 3); substr -------- 34 (1 row)
Example 10.19. Argument List Priority
create function testfun(text,integer)
returns text as $$ select 'text,integer' $$ language sql;
create function testfun(integer,text)
returns text as $$ select 'integer,text' $$ language sql;
select testfun('1'::text, '1'::text);
None of them are more suitable, so compare argument priority from left to right. Now the first argument is integer and text,
integer is higher than text, use testfun(integer,text).
Example 10.20. Insert and Update
create table tb(name varchar, age int); insert into tb(name,age) values(1223, '23'::varchar); INSERT 0 1 update tb set name=1343,age='24'::text; UPDATE 1 select * from tb; name | age ------+----- 1343 | 24 (1 row)
Column type and value type is not matched and LightDB does not have implicit cast rule between value type and column type by default. But by the compatible rules, LightDB can use explicit cast rule on it.
Type compatible works with operators,functions,insert and update.
Does not work in SELECT UNION, CASE(Section 10.5).