Wrong copy command output with postgres

Question

I'm trying to use the copy command to copy the content of a file into a database. One of the lines have this:

CCc1ccc(cc1)C(=O)/N=c\1/n(ccs1)C

and when i insert this normally into database there is no errors.

But when i'm trying to use the following command, this line is not insert correctly.

cat smile_test.txt | psql -c "copy testzincsmile(smile) from stdout" teste

This i what i get (it is wrong):

CCc1ccc(cc1)C(=O)/N=c/n(ccs1)C

What's wrong here?

Thank you :)

Craig Ringer · Accepted Answer · 2017-03-19 12:56:59Z

1

copy expects a specific input format and cannot just be used to read random text from a file into a field.

The specific issue you're hitting is probably a backslash being interpreted as an escape by the default copy in/out format.

answered Mar 19, 2017 at 12:56

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

1pa · Accepted Answer · 2017-03-19 13:10:39Z

0

I figure out how to do this:

This is my answer:

cat smile_test.txt | sed '1d; s/\\/\\\\/g' | psql -c "copy testzincsmile(smile) from stdout" teste

answered Mar 19, 2017 at 13:10

1pa

7552 gold badges9 silver badges24 bronze badges

Well, that's a workaround for this specific issue but not other possible problems, and it's still not really how copy should work.