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

This section describes some of the differences between Oracle and PG in character types, including how the data is stored, the amount of space actually occupied, and how the query returns data.

1. Differences

Data storage

Oracle

The length of a general definition, in Byte, or characters if (N char) is used

Such as:

-- in bytes, the actual storage length is bytes

Create table T1 (C1 varchar2 (2))

-- in characters. The actual storage length is related to the character & character set.

For example, GBK character set, 2 bytes of Chinese characters and 1 byte of ASCII code. The size depends on the actual stored data.

Create table T2 (C1 varchar2 (2 char));-- in characters

PG

The defined length in characters

The actual storage length is related to the character-character set, such as UTF8 character set, 3 bytes of Chinese characters and 1 byte of ASCII. The size depends on the actual stored data.

Query returned data

CHAR Typ

Oracle returns the actual stored data, including the spaces automatically filled by the database; the data returned by PG will remove the following spaces, whether the spaces are automatically filled by the database or the spaces that exist in the data itself.

Empty string

Oracle treats empty strings as null, while PG does not.

Second, test data

PG

UTF8 character set

Testdb=# create table tbl1 (var varchar (2), fixed char (2)); CREATE TABLEtestdb=# insert into tbl1 values ('test', 'test'); INSERT 0 1testdb=# insert into tbl1 values ('test 1'); INSERT 0 1testdb=# insert into tbl1 values ('test', 'test'); INSERT 0 1testdb=# select lpad (var,2,'*'), octet_length (var), lpad (fixed,2,'*'), octet_length (fixed) from tbl1 Lpad | octet_length | lpad | octet_length-+-Test | 6 | Test | 6 Test 1 | 4 | Test 1 | 4 * Test | 3 | * Test | 4 (3 rows) testdb=# create table tbl2 (var varchar (4) Fixed char (4)) CREATE TABLEtestdb=# insert into tbl2 values ('test', 'test'); INSERT 0 1testdb=# select lpad (fixed,4,'*'), octet_length (fixed) from tbl2; lpad | octet_length-+-* * Test | 8 (1 row)

Oracle

GBK character set

TEST-orcl@server4 > create table tbl1 (var varchar2 (2), fixed char (2)); Table created.TEST-orcl@server4 > insert into tbl1 values ('test', 'test'); insert into tbl1 values ('test', 'test') * ERROR at line 1:ORA-12899: value too large for column "TEST". "TBL1". "VAR" (actual: 4, maximum:2) TEST-orcl@server4 > insert into tbl1 values 1 row created.TEST-orcl@server4 > select lpad (var,2,'*'), lengthb (var), lpad (fixed,2,'*'), lengthb (fixed) from tbl1;LPAD LENGTHB (VAR) LPAD LENGTHB (FIXED)-11 2TEST-orcl@server4 > create table tbl2 (var varchar2 (2 char), fixed char (2 char)) Table created.TEST-orcl@server4 > TEST-orcl@server4 > insert into tbl2 values ('test', 'test'); 1 row created.TEST-orcl@server4 > insert into tbl2 values ('1'); 1 row created.TEST-orcl@server4 > select lpad (var,2,'*'), lengthb (var), lpad (fixed,2,'*'), lengthb (fixed) from tbl2 LPAD LENGTHB (VAR) LPAD LENGTHB (FIXED)-Test 4-select lpad (var,4,'*'), lengthb (var), lpad (fixed,4,'*'), lengthb (fixed) from tbl2 LPAD (VAR LENGTHB (VAR) LPAD (FIX LENGTHB (FIXED))-- Test 4, Test 4, Test 1 1 * * 1 2TEST-orcl@server4 >

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.