SQL Server data migration to PostgreSQL error interpretation and solution

Problem recurrence:

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 produces 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 the java program and insert it into PG, you will get the same error message:

invalid byte sequence for encoding "UTF8": 0x00

First of all, we believe that there is an error that cannot be converted when gb2312 is converted to UTF8. UTF8 is variable-length, 1-6 bytes. His coding 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

While 0x00 is in accordance with UTF8. This made us very surprised. Then we found two things that 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:http://stackoverflow.com/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:http://msdn.microsoft.com/en-us/library/ms191485.aspx

So we can make sure that pg handles null differently than SQL handles Server, so there is an error here.

PG specific code caused the problem are as follows (src/backend/utils/mb/wchar c pg_verify_mbstr_len) :

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)

The report_invalid_encoding function returns the error message, which is

invalid byte sequence for encoding "UTF8": 0x00
The real cause of the problem:
! IS_HIGHBIT_SET(*mbstr) enter the judgment when *mbstr is 0x00, and then determine whether *mbstr is \0. When it is \0, directly enter the function report_invalid_encoding to report an error.

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

Solution:

1. Modify 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:http://stackoverflow.com/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 the same as the first method.