I'm trying to insert data into some tables of a Postgres database with a bash script. I got it working like this

 psql -c '\copy raw.ap1_1 from file.csv with csv header;'

The problem is I wan't to give the copy command the Quote option, but I can't get it to work because of the quotes outside. I've tried wrapping single quotes in double quotes, double quotes in single quotes, using a backslash to escape quotes, etc.

What is want is something like this:

psql -c '\copy raw.ap1_1 from file.csv with csv header quote as '"';'

But of course I can't do that because the quotes are taken as part of the bash script. I either get a postgres syntax error, or I get a parsing EOF error (because the quotes override themselves).

CodePudding user response：

This should work:

psql -c "\\copy raw.ap1_1 from file.csv (format 'csv', header, quote '\"')"

CodePudding user response：

PostgreSQL's SQL offers an alternative quoting method called dollar quoting. That is probably the easiest way to do it.

psql -c '\copy raw.ap1_1 from file.csv with csv header quote as $$"$$'

Note that neither dollar signs nor double quotes are special inside shell single quotes. Also note that \copy doesn't end with a ;. Further note that specifying a quote character which is the same as the default seems a bit silly.

If you don't want to use dollar quoting, you could escape correctly at the shell level. You can't do that from within the shell's single quote so first you have to close the quotes, then print '"' each one escaped with a backslash. (If this were not the end of the line, you would then re-open the shell's single quote to finish the line.)

    psql -c '\copy raw.ap1_1 from file.csv with csv header quote as '\'\"\'

CodePudding user response：

First of all ,you will login . Then

Copy table_name from 'C:\\\\expdata_202111122321.csv' with delimiter ';' csv header encoding 'windows-1254';