无法使用pg_dump将表复制到另一个数据库 [英] Can't copy table to another database with pg_dump

问题描述

我正在尝试将表从一个数据库复制到另一个数据库（NOT模式）。我在终端中使用的代码如下：

I'm trying to copy a table from one database to another database (NOT schema). The code I used in terminal is as below:

pg_dump -U postgres -t OldSchema.TableToCopy OldDatabase | psql -U postgres -d NewDatabase

当我按下Enter键时，它要求输入postgres密码，然后输入密码它要求输入psql密码。输入它，然后按Enter。我收到很多：

When I press Enter it requests postgres password I enter my pass and then It requests psql password. I enter it and press Enter. I receive lots of:

invalid command \N
ERROR: relation "TableToCopy" does not exist

两个表都具有 UTF8 编码。我在做错什么吗？

操作系统：Windows XP

Both tables have UTF8 encoding. Am I doing something wrong?
OS: windows XP

错误输出：

psql:TblToCopy.sql:39236: invalid command \N
psql:TblToCopy.sql:39237: invalid command \N
psql:TblToCopy.sql:39238: invalid command \N
.
.
.

在上述几百个错误之后，终端回显：

After Hundreds of above errors, the terminal echoes:

psql:TblToCopy.sql:39245: ERROR: syntax error at or near "509"
LINE 1: 509 some gibberish words and letters here

最后：

sql:TblToCopy.sql:39245: ERROR: relation "TableToCopy" does not exist

编辑
我阅读了对同一问题的回复 ppsql的N错误，它说使用INSERT而不是COPY，但是在文件pg_dump中创建了COPY。怎么说对pg_dump使用INSERT而不是COPY？

EDIT I read this response to the same problem \N error with psql , it says to use INSERT instead of COPY, but in the file pg_dump created COPY. How to say to pg_dump to use INSERT instead of COPY?

我将iconv文件转换为utf-8。现在，该错误已消失，但我有一个新错误。在这种特殊情况下，当我使用psql将数据导入数据库时​​，会发生一些新的情况。表已创建但没有数据。它说：

I converted the file with iconv to utf-8. Now that error has gone but I have a new error. In this particular case when I use psql to import data to database something new happens. Table gets created but without data. It says:

SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
psql:tblNew.sql:39610: ERROR:  value too long for type character(3)
CONTEXT:  COPY words, line 1, column first_two_letters: "سر"
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER

推荐答案

我尝试用Encoding创建数据库：UTF8和一个表，并插入COPY命令尝试插入的两个UTF-8编码字符，并且在使用INSERT时可以使用。

I've tried to create a database with Encoding: UTF8 with a table and insert the two UTF-8 encoded characters the COPY command is trying to insert and it works when using INSERT.

CREATE DATABASE test
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;

CREATE TABLE x
(
  first_two_letters character(3)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE x
  OWNER TO postgres;

INSERT INTO x(
            first_two_letters)
    VALUES ('سر');

根据 http://rishida.net/tools/conversion/ 对于失败的COPY，Unicode代码点为：

According to http://rishida.net/tools/conversion/ for the failing COPY the Unicode code points are:

U + 0633 U + 0631

U+0633 U+0631

这是两个字符，这意味着您应该能够将它们存储在定义为character（3）的列中，该列最多存储3个字符（不是字节）的字符串。

which are two characters, which means you should be able to store them in a column defined as character(3), which stores strings up to 3 characters (not bytes) in length.

如果我们尝试插入，它将成功：

and if we try to INSERT, it succeeds:

 INSERT INTO x( 
                first_two_letters) 
        VALUES (U&'\0633\0631');

来自 pgdump文档，您可以使用--inserts选项插入而不是COPY

From the pgdump documentation you can INSERT instead of COPY by using the --inserts option

-插入
转储数据作为INSERT命令（而不是COPY）。这会使恢复非常缓慢。它主要用于制作可以将
加载到非PostgreSQL数据库中的转储。但是，由于此选项
会为每一行生成一个单独的命令，因此在重新加载行
时发生错误只会导致该行丢失，而不是整个表内容丢失。
请注意，如果重新排列了
列顺序，则恢复可能会完全失败。 --column-inserts选项可以安全地防止列顺序
的更改，尽管速度甚至更慢。

--inserts Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

尝试使用此选项对于步骤1：

Try to use this instead for Step 1:

pg_dump -U postgres -t OldSchema."TableToCopy" --inserts OldDatabase > Table.sql

我也尝试过从表复制到文件并使用COPY导入

I've also tried to COPY from a table to a file and use COPY to import and for me it works.

您确定您的客户端和服务器数据库编码为UTF8吗？

Are you sure your client and server database encoding is UTF8 ?

首先，将名为 x的表从数据库测试上的模式公共导出到纯文本SQL文件：

Firstly, export the table named "x" from schema "public" on database "test" to a plain text SQL file:

pg_dump -U postgres -t public."x" test > x.sql

创建包含以下内容的x.sql文件：

which creates the x.sql file that contains:

--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: x; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE x (
    first_two_letters character(3)
);


ALTER TABLE public.x OWNER TO postgres;

--
-- Data for Name: x; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY x (first_two_letters) FROM stdin;
سر 
\.


--
-- PostgreSQL database dump complete
--

其次，使用以下命令导入：

psql -U postgres -d test -f x.sql

Secondly, import with:
psql -U postgres -d test -f x.sql

这篇关于无法使用pg_dump将表复制到另一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！