正确的COPY命令从具有单引号数据的csv文件加载postgreSQL数据? [英] The correct COPY command to load postgreSQL data from csv file that has single-quoted data?
本文介绍了正确的COPY命令从具有单引号数据的csv文件加载postgreSQL数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个csv文件,其内容如下:
I have csv file that has contents like this:
10,53073,0,0,'Y','2008-05-30 21:46:55',0,'2008-05-30 21:48:04',0,53071,2
我想将csv数据加载到my_table中。
I want to load the csv data into my_table.
CREATE TABLE my_table
(
ad_tree_id numeric(10,0) NOT NULL,
node_id numeric(10,0) NOT NULL,
ad_client_id numeric(10,0) NOT NULL,
ad_org_id numeric(10,0) NOT NULL,
isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
created timestamp without time zone NOT NULL DEFAULT now(),
createdby numeric(10,0) NOT NULL,
updated timestamp without time zone NOT NULL DEFAULT now(),
updatedby numeric(10,0) NOT NULL,
parent_id numeric(10,0),
seqno numeric(10,0),
CONSTRAINT ad_treenodemm_pkey PRIMARY KEY (ad_tree_id , node_id ),
CONSTRAINT adtree_adtreenodemm FOREIGN KEY (ad_tree_id)
REFERENCES ad_tree (ad_tree_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT ad_treenodemm_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
)
当我运行此命令时在pgAdmin III工具中:
When I run this command in pgAdmin III tool:
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV;
我遇到了错误:
ERROR: value too long for type character(1)
CONTEXT: COPY my_table, line 1, column isactive: "'Y'"
然后我像这样修改命令:
Then I modified the command like this:
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE ''';
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '\'';
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '\'' ESCAPE '\';
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '\'' ESCAPE \;
尝试时全部失败。
所以,任何人都可以在这种情况下向我显示正确的COPY命令?
So, anyone can show me the correct COPY command for this case?
推荐答案
双引号(如果 standard_conforming_strings
处于打开状态,请参见文档)
Double single quotes (if standard_conforming_strings
is on, see the docs)
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '''';
或使用非标准的PostgreSQL特定转义字符串:
or use the non-standard PostgreSQL-specific escape string:
COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE E'\'';
这篇关于正确的COPY命令从具有单引号数据的csv文件加载postgreSQL数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文