The correct COPY command to load postgreSQL data from csv file that has single-quoted data?

Question

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

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]))
)

When I run this command in pgAdmin III tool:

COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV;

I got error:

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 \;

All failed when tried.

So, anyone can show me the correct COPY command for this case?

Craig Ringer · Accepted Answer · 2012-10-12 12:13:41Z

41

Double single quotes (if standard_conforming_strings is on, see the docs)

COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '''';

or use the non-standard PostgreSQL-specific escape string:

COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE E'\'';

edited Oct 12, 2012 at 12:13

answered Oct 12, 2012 at 10:27

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ForeverLearner Over a year ago

If COPY is reserved for the admin users, from psql command line use, \COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '''';

Craig Ringer Over a year ago

@Tammy \copy gets paths relative to the psql client, COPY gets them relative to the server host.

Patrick Parker · Accepted Answer · 2018-03-01 15:28:34Z

6

Some other people who are experiencing this error may want to check the file to see if it contains a header on the first line. While it wasn't the problem in your case, it is worth noting the way to work around it:

COPY my_table FROM 'c:\downloads\file.csv' WITH DELIMITER ',' CSV HEADER;

edited Mar 1, 2018 at 15:28

Patrick Parker

5,0074 gold badges24 silver badges57 bronze badges

answered Mar 1, 2018 at 12:52

Rupasa Sushma

1052 silver badges11 bronze badges

Comments

null · Accepted Answer · 2012-10-12 10:20:16Z

4

Never mind, I got the answer:

COPY my_table FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '''';

answered Oct 12, 2012 at 10:20

null

9,19417 gold badges72 silver badges99 bronze badges

Comments

Adriaan · Accepted Answer · 2023-02-22 09:51:03Z

0

Use this if you have column data present with double quotes in .csv files separated by ,

COPY tablename FROM 'c:\downloads\file.csv' DELIMITERS ',' CSV QUOTE '"';

edited Feb 22, 2023 at 9:51

Adriaan

18.2k7 gold badges47 silver badges88 bronze badges

answered Feb 22, 2023 at 9:49

ManojRawat

811 silver badge10 bronze badges

Collectives™ on Stack Overflow

The correct COPY command to load postgreSQL data from csv file that has single-quoted data?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related