Shulou(Shulou.com)06/01 Report--

This section takes numerical conversion and numerical and character conversion as examples to introduce some of the similarities and differences between Oracle and PostgreSQL type conversion, which can be extended to other types.

I. numerical type conversion

The following examples are numerical types, including conversion of operation results and forced type conversion.

Calculation result

Take the division operation as an example.

Division operation of PostgreSQL

Testdb=# select 1max 4;? column?-0 (1 row)

Division operation of Oracle

TEST-orcl@server4 > select 1 from dual; 4 from dual; 1 Compact 4-.25

The two integer values 1 and 4 participate in the division operation, and the result is that the Oracle whose PostgreSQL is an integer is 0.25 of the floating-point type, and their behaviors are inconsistent.

Why is the result returned by PostgreSQL performing an integer operation an integer? Of course, this is due to the mechanism of PG (integers / integers = integers). In PG, the result type of the operation can be obtained by querying pg_operator:

Testdb=#\ xExpanded display is on.testdb=# select * from pg_operator where oprname ='/ 'and oprleft=21 and oprright = 21 -[RECORD 1] +-oprname | /-- > operator oprnamespace | 11oprowner | 10oprkind | boprcanmerge | foprcanhash | foprleft | 21-> int2 (2-byte integer, which can be queried by select * from pg_type where oid=21) oprright | 21-- > oprresult above | 21-> integer / integer, the result is also integer oprcom | 0oprnegate | 0oprcode | int2divoprrest |-oprjoin |-

In PostgreSQL, to get a result of 0.25, you need to convert:

Testdb=# select 1max 4V row float; column?-0.25 (1 float) II. Forced type conversion

Take the character type-> integer type as an example.

PostgreSQL

Testdb=# drop table if exists t_cast; DROP TABLEtestdb=# create table t_cast (c_int int,c_s varchar (20)); CREATE TABLEtestdb=# insert into t_cast values (1 row); INSERT 0 1testdb=# insert into t_cast values (2 recorder 2'); INSERT 0 1testdb=# select * from t_cast where c_int = 1; c_int | clocks-+-1 | 1 (1 row) testdb=# select * from t_cast where cations = 1 ERROR: operator does not exist: character varying = integer-- > variable length character conversion to integer LINE 1: select * from t_cast where clocks = 1; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Oracle

TEST-orcl@server4 > drop table tactile castings table dropped.TEST-orcl@server4 > create table t_cast (c_int int,c_s varchar2 (20)) tablespace users;Table created.TEST-orcl@server4 > insert into t_cast values (1 row created.TEST-orcl@server4 > insert into t_cast values); 1 row created.TEST-orcl@server4 > select * from t_cast where c_int = 1 C_INT Clippers-- 1 1TEST-orcl@server4 > select * from t_cast where clocks = 1; C_INT Clippers-- 1 1

PG, integers cannot be converted to character types, while Oracle can.

PG can convert character-> integer types through explicit type conversions or custom type conversions:

-- explicit conversion testdb=# select * from t_cast where caches = 1 row; c_int | clocks-+-1 | 1 (1 row)-- Custom type conversion testdb=# create cast (varchar as integer) with inout as implicit;CREATE CASTtestdb=# select * from t_cast where varchars = 1; c_int | cans-+-1 | 1 (1 type)

You can query the type conversions supported by PG through the data dictionary table pg_cast.

Testdb=# select oid,a.* from pg_cast a where castsource=1043 and casttarget = 23 Oid | castsource | casttarget | castfunc | castcontext | castmethod-+-16774 | 1043 | 23 | 0 | I | I-> this is a newly added record. III. References

CREATE CAST

PostgreSQL Custom automatic Type conversion (CAST)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.