COPY Command with comma in string values - Postgresql 10

Question

I am trying to copy the contents of a dat file to a table in PostgreSQL but getting errors. The dat file has the following:

1,'"{{text}}","{{NoName}}":"{test},{OneName}:{test}"'

Table Schema:

Create Table TestInfo (ID int, TestValues text);

I run the following query:

COPY Test from '/opt/Data/TestInfo.dat' WITH DELIMITER AS ',' NULL AS '';

but get the following error

ERROR:  extra data after last expected column

Even tried

COPY Test from '/opt/Data/TestInfo.dat' WITH DELIMITER AS ',' csv;

still the same error.... Can some one please help me getting the contents of the dat file copied to the DB?

Thanks

khampson · Accepted Answer · 2018-03-02 01:45:23Z

1

I suspect this is because because the default quote character for the copy command in Postgres is a double quote, and it appears in your csv, it's a single quote instead.

Try this:

COPY Test from '/opt/Data/TestInfo.dat' WITH DELIMITER AS ',' QUOTE AS '''' NULL AS '';

Note the need to escape the single quote, resulting in 4 single quotes.

answered Mar 2, 2018 at 1:45

khampson

15.5k4 gold badges45 silver badges44 bronze badges

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

2 Comments

P_Ar Over a year ago

Worked like a charm.. Thanks

khampson Over a year ago

Sure, no problem.

Collectives™ on Stack Overflow

COPY Command with comma in string values - Postgresql 10

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related