What is the method of writing Oracle and PostgreSQL data

This article mainly introduces "Oracle and PostgreSQL data writing method is what", in daily operation, I believe many people in Oracle and PostgreSQL data writing method is what problem there is doubt, Xiaobian consulted all kinds of information, sorted out simple and easy to use operation method, hope to answer everyone "Oracle and PostgreSQL data writing method is what" doubt helpful! Next, please follow the small series to learn together!

On special characters (invisible characters such as ASCII 0, control characters, illegal characters, etc.), Oracle is very lenient and can write basically any data, including data that does not conform to encoding rules. PostgreSQL, on the other hand, must conform to the encoding of the character set, for example, in the UTF8 character set, the character encoding entered must conform to the UTF8 encoding.

Oracle

Character set GBK, create data table, insert special character (0x00), no problem

TEST-orcl@DESKTOP-V430TU3>drop table t_0x00 purge;Table dropped.TEST-orcl@DESKTOP-V430TU3>create table t_0x00(id int,c1 varchar2(200),c2 blob);Table created.TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(1,chr(0),null);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(2,'c1'||chr(0),null);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(3,chr(0)||'c1',null);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(4,'c1'||chr(0)||'c1',null);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(5,'c1'||chr(0)||'c1',to_blob(HEXTORAW('550055')));1 row created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>select * from t_0x00 where c1 like '%'||chr(0)||'%'; ID C1 C2---------- -------------------- -------------------- 1 2 c1 3 c1 4 c1 c1 5 c1 c1 550055TEST-orcl@DESKTOP-V430TU3>

PostgreSQL

Character set is UTF8, create data table, insert special character (0x00), cannot insert

[local:/data/run/pg12]:5120 pg12@testdb=# \encodingUTF8[local:/data/run/pg12]:5120 pg12@testdb=# drop table t_0x00;insert into t_0x00 values(4,'c1'||E'\x00'||'c1',null);insert into t_0x00 values(5,'c1'||E'\x00'||'c1','\x550055'::bytea);DROP TABLE[local:/data/run/pg12]:5120 pg12@testdb=# create table t_0x00(id int,c1 varchar(200),c2 bytea);CREATE TABLE[local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(1,E'\x00',null);ERROR: invalid byte sequence for encoding "UTF8": 0x00[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(1,chr(0),null);ERROR: null character not permitted[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(2,null,bytea '\x00');INSERT 0 1[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(3,E'\x00'||'c1',null);ERROR: invalid byte sequence for encoding "UTF8": 0x00[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(4,'c1'||E'\x00'||'c1',null);ERROR: invalid byte sequence for encoding "UTF8": 0x00[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(5,'c1'||E'\x00'||'c1','\x550055'::bytea);ERROR: invalid byte sequence for encoding "UTF8": 0x00[local:/data/run/pg12]:5120 pg12@testdb=# select * from t_0x00 where c1 like '%'||chr(0)||'%';ERROR: null character not permitted[local:/data/run/pg12]:5120 pg12@testdb=#

These special characters are considered during Oracle -> PG migration.

At this point, about "Oracle and PostgreSQL data writing method is what" learning is over, I hope to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!