Psql finds path when connected to the database but not when running command with -c

Question

I'm trying to loop through a directory, copying all the json files into my PostgreSQL database. As I copy them, I must replace any \ with \\ so that Postgres can properly parse it.

When I run:

$ psql -p 30001 my_database
my_database=#\copy my_jsons from program 'sed ''s/\\/\\\\/g'' < /home/postgres/jsons.json';

This command works fine and properly inserts the data into the table. However, since I have multiple files I need to copy, I am trying to loop through a directory and insert using:

$ psql -p 30001 my_database -c "\copy my_jsons from program 'sed ''s/\\/\\\\/g'' < /home/postgres/jsons.json';"

Using this command, sed starts acting differently and I get the error:

sed: -e expression #1, char 8: unterminated `s' command

Does anyone know why sed acts differently while using it in this way, and what I can do to get my desired result?

Answer 1

Your escaping \\ are interpreted by the shell. This yields the following PostgreSQL command:

\copy my_jsons from program 'sed ''s/\/\\/g'' < /home/postgres/jsons.json';

You can see that the sed command has been altered in a way it is no longer valid, hence the error message.

Solution 1

Escape twice :

$ psql -p 30001 my_database -c "\\copy my_jsons from program 'sed ''s/\\\\/\\\\\\\\/g'' < /home/postgres/jsons.json';"

This is ugly and difficult to read and maintain.

Solution 2

Don't use psql's -c option and give your command through the standard input (Bash's here-document here):

psql -p 30001 my_database <<- "_END_"
    \copy my_jsons from program 'sed ''s/\\/\\\\/g'' < /home/postgres/jsons.json';
_END_