SQL Server資料移轉至PostgreSQL出錯的解釋以及解決方案_PostgreSQL

來源:互聯網
上載者:User

問題重現:

1、PG用戶端:

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

2、SQL Server產生資料

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' + '');

然後通過java程式進行擷取資料並插入到PG,同樣會得到錯誤資訊:

invalid byte sequence for encoding "UTF8": 0x00

首先我們認為此為gb2312轉化到UTF8時,發生了無法轉化的錯誤。經查UTF8是變長的, 1-6個位元組。他的編碼規則如下:

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

而0x00是符合UTF8規則的。這就使我們非常詫異。然後我們發現有兩點繼而確認了問題:
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

由此我們確定,是pg對null的處理和SQL Server處理是不相同的,所以在這裡出現了錯誤。

而導致這一問題的PG具體代碼如下(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)

report_invalid_encoding函數是將錯誤資訊返回,也就是

invalid byte sequence for encoding "UTF8": 0x00
而真正導致這一問題的就是:
!IS_HIGHBIT_SET(*mbstr)當*mbstr為0x00時進入判斷,然後進而判斷*mbstr是否為\0,當為\0時,直接進入函數report_invalid_encoding報錯。

所以出現此問題的原因是PG和SQL Server對null的處理是不相同的。

處理方案 :

1、將SQL Server來源資料進行修改方法,

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

2、對應用進行修改,擷取到SQL Server資料時,將資料進行轉化,和第一種方法異曲同工。

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.