PostgreSQL:将数据从 SQL Server 2008 R2 导出到 PostgreSQL 9.5 [英] PostgreSQL: Export data from SQL Server 2008 R2 to PostgreSQL 9.5

问题描述

我有一个表可以将数据从 SQL Server 导出到 PostgreSQL.

我遵循的步骤:

步骤 1:从 SQL Server 导出数据:

来源:SQL Server 表目的地:平面文件目的地要复制的表或查询:查询

查询:

SELECTCOALESCE(convert(varchar(max),id),'NULL') + '|'+COALESCE(convert(varchar(max),Name),'NULL') + '|'COALESCE(convert(varchar(max),EDate,121),'NULL') AS A从 tbl_Employee;

文件名:file.txt

第 2 步:复制到 PostgreSQL.

命令:

\COPY tbl_employee FROM '$FilePath\file.txt' DELIMITER '|'NULL AS 'NULL' 编码 'LATIN1'

获取以下错误消息:

错误:编码UTF8"的字节序列无效:0xc1 0x20

解决方案

你告诉 Postgres 源代码将被编码为 LATIN1:

<块引用>

\copy ... ENCODING 'LATIN1'

但事实并非如此，或者文件已损坏.否则我们将看不到错误消息.'$FilePath\file.txt' 的真正编码是什么?

当前的 client_encoding 与此无关，因为 引用COPY 上的手册:

<块引用>

编码

指定文件在 encoding_name 中编码.如果省略此选项，则使用当前客户端编码.

(\copy 只是 psql 中 SQL COPY 的包装器.)

而且您的 server_encoding 在很大程度上也无关紧要 - 只要 Postgres 可以使用 内置转换，目标编码包含源编码的所有字符 - LATIN1 就是这种情况 -> UTF8:iso_8859_1_to_utf8代码>.

所以剩下的错误来源是你的文件，它几乎肯定不是有效的 LATIN1.

I have a table to export data from SQL Server to PostgreSQL.

Steps I followed:

Step 1: Export data from SQL Server:

Source: SQL Server Table
Destination: Flat file Destination 
Table Or Query to copy: Query 

Query:

SELECT 
    COALESCE(convert(varchar(max),id),'NULL') + '|'
    +COALESCE(convert(varchar(max),Name),'NULL') + '|'
    COALESCE(convert(varchar(max),EDate,121),'NULL') AS A
FROM tbl_Employee;

File Name: file.txt

Step 2: Copy to PostgreSQL.

Command:

\COPY tbl_employee FROM '$FilePath\file.txt' DELIMITER '|' NULL AS 'NULL' ENCODING 'LATIN1'

Getting Following error message:

ERROR:  invalid byte sequence for encoding "UTF8": 0xc1 0x20

解决方案

You tell Postgres the source would be encoded as LATIN1:

\copy ... ENCODING 'LATIN1'

But that's either not the case or the file is damaged. Else we would not see the error message. What is the true encoding of '$FilePath\file.txt'?

The current client_encoding is not relevant for this since, quoting the manual on COPY:

ENCODING

Specifies that the file is encoded in the encoding_name. If this option is omitted, the current client encoding is used.

(\copy is jut a wrapper for SQL COPY in psql.)

And your server_encoding is largely irrelevant, too - as long as Postgres can use a built-in conversion and the target encoding contains all characters of the source encoding - which is the case for LATIN1 -> UTF8: iso_8859_1_to_utf8.

So the remaining source of error is your file, which is almost certainly not valid LATIN1.

这篇关于PostgreSQL:将数据从 SQL Server 2008 R2 导出到 PostgreSQL 9.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！