Postgres Psql Copy statement, syntax error on path name for csv import quotes

Question

I've got a psql script where I'm trying to import a CSV into a table that is already created but has no data in it, but I'm getting a syntax error on the COPY statement path name and I can't figure out why.

The statement:

COPY legislators (last_name, first_name, middle_name, suffix, nickname, full_name, birthday, gender, type, state, district, senate_class, party, url, address, phone, contact_form, rss_url, twitter, facebook, youtube, youtube_id, bioguide_id, thomas_id, opensecrets_id, lis_id, fec_ids, cspan_id, govtrack_id, votesmart_id, ballotpedia_id, washington_post_id, icpsr_id, wikipedia_id) FROM ‘/User/matthewa/Desktop/push-thought-misc/legislators-current.csv’ DELIMITER ‘,’ CSV HEADER;

Error I'm getting:

Other outputs showing path name pushthought=# ! pwd
/Users/matthewa

I've tried every iteration of ~/, double quotes, single quotes, I just can't get the syntax right.

The table I created that I'm trying to copy into is here, but I can create the table fine.

CREATE TABLE legislators (
id SERIAL PRIMARY KEY,
last_name VARCHAR,
first_name VARCHAR,
middle_name VARCHAR,
suffix VARCHAR,
nickname VARCHAR,
full_name VARCHAR,
birthday DATE,
gender VARCHAR, 
type VARCHAR,   
state VARCHAR,  
district VARCHAR,   
senate_class VARCHAR,
party VARCHAR,
url VARCHAR,
address VARCHAR,
phone VARCHAR,
contact_form VARCHAR,
rss_url VARCHAR,
twitter VARCHAR,
facebook     VARCHAR,
youtube VARCHAR,
youtube_id VARCHAR,
bioguide_id VARCHAR,
thomas_id VARCHAR,
opensecrets_id VARCHAR,
lis_id   VARCHAR,
fec_ids VARCHAR,
cspan_id VARCHAR,
govtrack_id VARCHAR,
votesmart_id VARCHAR,
ballotpedia_id VARCHAR,
washington_post_id VARCHAR, 
icpsr_id VARCHAR,
wikipedia_id VARCHAR
);

Can anyone give me any more suggestions to try? Thanks in advance.

Thanks @Mike67, that was it. I didn't even know smart quotes were a thing. From now on I won't write psql scripts in my mac Notes app. Text editors I notice default to straight single quotes. Just FYI to any readers, In the end to get it fully working I had to use \COPY instead of COPY, which I'll investigate separately. Thanks Mike — Boingoloid
– Boingoloid, Commented Aug 1, 2020 at 19:19

Mike67 · Accepted Answer · 2020-08-01 18:23:18Z

2

Remove the smart quotes from your query and replace with single quote. This should work:

COPY legislators (last_name, first_name, middle_name, suffix, nickname, full_name, birthday, gender, type, state, district, senate_class, party, url, address, phone, contact_form, rss_url, twitter, facebook, youtube, youtube_id, bioguide_id, thomas_id, opensecrets_id, lis_id, fec_ids, cspan_id, govtrack_id, votesmart_id, ballotpedia_id, washington_post_id, icpsr_id, wikipedia_id) FROM '/User/matthewa/Desktop/push-thought-misc/legislators-current.csv' DELIMITER ',' CSV HEADER;

answered Aug 1, 2020 at 18:23

Mike67

11.3k2 gold badges9 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Postgres Psql Copy statement, syntax error on path name for csv import quotes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related