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…

Accepted Answer

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

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

Solution

Problem