[Windows 10]Problem escaping dollar sign contained in tablename for /copy command in psql

Question

I am trying to setup a shell script to insert data from .csv files into postgreSQL. As a first test I just tried rto do it in terminal command, and I am running into an issue because my table has a '$' in its name and I was not able to come up with the proper way to escape it so far.

My command look like this :

psql -d trm -h localhost -p 5432 -U postgres -c "\COPY ts_dev.tbl$trm_secndry_readings from 'FILEPATH' with delimiter ',' ;"

I have made several test, with this command, if tablename='test1' the command works, but if tablename='prefix&test1' then the command fails as terminal is trying to parse what is after '$' as a variable name

So for example :

psql -d trm -h localhost -p 5432 -U postgres -c "\COPY schtest.prefix$name from 'FILEPATH' with delimiter ',' ;"

ERROR: relation "schtest.prefix" does not exist

I'm using Windows powershell.

Answer 1

In both POSIX-compatible shells such as bash and in PowerShell, $ is a metacharacter, which - in unquoted use or inside "..." - requires escaping in order to be interpreted verbatim (in order to be passed through to a command as-is).

However, the escape character differs between these two shells:

• For POSIX-compatible shells it is \:

psql -d trm -h localhost -p 5432 -U postgres -c "\COPY ts_dev.tbl\$trm_secndry_readings from 'FILEPATH' with delimiter ',' ;"

• For PowerShell it is ` (the so-called backtick; see about_Special_Characters):

psql -d trm -h localhost -p 5432 -U postgres -c "\COPY ts_dev.tbl`$trm_secndry_readings from 'FILEPATH' with delimiter ',' ;"