没有合适的资源?快使用搜索试试~ 我知道了~
SQL Server数据迁移至PostgreSQL出错的解释以及解决方案
2 下载量 53 浏览量
2020-12-15
21:27:16
上传
评论
收藏 53KB PDF 举报
温馨提示
试读
2页
问题重现: 1、PG客户端: 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产生数据 create table test_varchar(id int,name varchar(20)); insert into test_varchar values (1, '
资源详情
资源评论
SQL Server数据迁移至数据迁移至PostgreSQL出错的解释以及解决方案出错的解释以及解决方案
问题重现:
1、PG客户端:
postgres=# create table text_test (id int,info text);
CREATE TABLE
postgres=# insert into text_test values (1,E' x00');
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 ( x00) 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
This is the default field terminator.
Newline character
This is the default row terminator.
Carriage return/line feed
Backslash1 \
Null terminator (nonvisible terminator)2