导出到PostgreSQL时出现SQL Server导入和导出向导错误 [英] SQL Server Import and Export Wizard Error when exporting to PostgreSQL

问题描述

我正在尝试使用SQL Server的导入和导出向导（SQL Server 2008 R2）导出SQL Server数据库。在目标PostgreSQL数据库中可以很好地创建数据库模式，因此大多数数据也可以。仅当我尝试导出第一行中具有 NULL 值的列时，它才会崩溃：

I am trying to export a SQL Server database using SQL Server's Import and Export Wizard (SQL Server 2008 R2). The database schema gets created fine in the destination PostgreSQL database and so does most of the data. It only crashes when I try to export a column which has a NULL value in the first row:


错误0xc020844b：数据流任务22：数据插入期间发生异常，提供程序返回的消息为：类型为System.Int32的对象。 ]。
（SQL Server导入和导出向导）


不幸的是相关例外是荷兰语，但它说无法将类型 System.Int32 的对象转换为 System.Char [] 。在生成的PostgreSQL数据库中，原始SQL Server架构中的列类型为 int 和 integer 。

Unfortunately the relevant exception is in Dutch, but it says it can't convert an object of type System.Int32 to System.Char[]. The type of the column in the original SQL Server schema is int and integer in the resulting PostgreSQL database.

仅当第一行中的列值为 NULL 时才会发生。我认为它会尝试根据列的第一个值来推断数据的类型，如果第一个值是 System.Char [] > NULL 。是否有任何方法可以更改此行为，并以与所得PostgreSQL列的类型相同的方式推断数据的类型（因为这样做正确）？

This only happens when the value of a column in the first row is NULL. I think it tries to infer the type of the data based on the first value of the column and defaults to System.Char[] if the first value is NULL. Is there any way to change this behaviour and let the type of the data be inferred in the same way as the type of the resulting PostgreSQL column (because it does that correctly)?

推荐答案

问题似乎并不在PostgreSQL上，所以您可能应该在转换工具上寻找该错误。从psql：

The problem doesn't seem to be on the PostgreSQL side, so you should probably be looking to the conversion tool for the bug. From psql:

test=# CREATE TABLE import (id int NOT NULL PRIMARY KEY, val1 int, val2 int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "import_pkey" for table "import"
CREATE TABLE
test=# COPY import FROM STDIN (FORMAT CSV);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1,,111
>> 2,22,222
>> \.
test=# SELECT * FROM import;
 id | val1 | val2 
----+------+------
  1 |      |  111
  2 |   22 |  222
(2 rows)

这篇关于导出到PostgreSQL时出现SQL Server导入和导出向导错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！