更新时间:2023-02-02 20:17:53
我尝试用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