Explanation and solution for errors in SQL Server data migration to PostgreSQL

The problem reappears:

1. PG client:

postgres=# create table text_test (id int,info text);
CREATE TABLE
postgres=# insert into text_test values (1,E'\0x00');
ERROR: invalid byte sequence for encoding "UTF8": 0x00

2. SQL Server generates data

create table test_varchar(id int,name varchar(20));
insert into test_varchar values (1, 'name' + char(0));
insert into test_varchar values (1, 'name' + '');

Then, get the data through a java program and insert it into PG, and you will also get the error message:

invalid byte sequence for encoding "UTF8": 0x00

First of all, we think that this is when gb2312 is converted to UTF8, an error that cannot be converted occurred. UTF8 was found to be variable length, 1-6 bytes. His encoding rules are as follows:

Bits	Last code point	Byte 1	Byte 2	Byte 3	Byte 4	Byte 5	Byte 6
7	U+007F	0xxxxxxx
11	U+07FF	110xxxxx	10xxxxxx
16	U+FFFF	1110xxxx	10xxxxxx	10xxxxxx
21	U+1FFFFF	11110xxx	10xxxxxx	10xxxxxx	10xxxxxx
26	U+3FFFFFF	111110xx	10xxxxxx	10xxxxxx	10xxxxxx	10xxxxxx
31	U+7FFFFFFF	1111110x	10xxxxxx	10xxxxxx	10xxxxxx	10xxxxxx	10xxxxxx

And 0x00 complies with UTF8 rules. This surprised us very much. Then we found two points and then confirmed the problem:
1、

PostgreSQL doesn't support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

If you need to store the NULL character, you must use a bytea field - which should store anything you want, but won't support text operations on it.

Given that PostgreSQL doesn't support it in text values, there's no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it's likely going to be easier to do that in preprocessing before you load it.

Source:/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0

2、

Terminating character	Indicated by
Tab	\t This is the default field terminator.
Newline character	\n This is the default row terminator.
Carriage return/line feed	\r
Backslash1	\\
Null terminator (nonvisible terminator)2	\0
Any printable character (control characters are not printable, except null, tab, newline, and carriage return)	(*, A, t, l, and so on)
String of up to 10 printable characters, including some or all of the terminators listed earlier	(\t, end, !!!!!!!!!!, \t—\n, and so on)

Source:/en-us/library/

From this we have determined that pg's processing of null is different from SQL Server, so an error occurs here.

The specific PG code that causes this problem is as follows (pg_verify_mbstr_len of src/backend/utils/mb/):

if (!IS_HIGHBIT_SET(*mbstr))
    {
      if (*mbstr != '\0')
      {
        mb_len++;
        mbstr++;
        len--;
        continue;
      }
      if (noError)
        return -1;
      report_invalid_encoding(encoding, mbstr, len);
    }

#define IS_HIGHBIT_SET(ch)   ((unsigned char)(ch) & HIGHBIT)
#define HIGHBIT         (0x80)

Report_invalid_encoding function returns the error message, that is,

invalid byte sequence for encoding "UTF8": 0x00
What really causes this problem is:
!IS_HIGHBIT_SET(*mbstr) When *mbstr is 0x00, it enters the judgment, and then determines whether *mbstr is \0. When it is \0, it directly enters the function report_invalid_encoding and reports an error.

So the reason for this problem is that PG and SQL Server handle null differently.

Solution:

1. Method for modifying SQL Server source data,

UPDATE: This seems to work:
 
Select * from TABLE
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
So:
 
Update TABLE
SET naughtyField = SUBSTRING(naughtyField, 1, LEN(naughtyField) - 1)
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
Source:/questions/3533320/sql-server-remove-end-string-character-0-from-data

2. Modify the application and convert the data when obtaining SQL Server data, which is similar to the first method.