Escape double quotes in a command within multiline string concatenation

Question

I am trying to run psql \copy command from within an Azure DevOps pipeline which is working fine for all my tables but the table where our table name contains dashes: my-table-name.

Task script:

- task: PowerShell@2
    displayName: "Populate data"
    inputs:
      targetType: 'inline'
      script: |
        #Setup PostgreSQL
        Set-Service postgresql-x64-14 -StartupType manual
        Start-Service postgresql-x64-14
        Get-CimInstance win32_service | Where-Object Name -eq "postgresql-x64-14"
        #Create tables in PostgreSql database
        $PG_SQL = "C:\Program Files\PostgreSQL\14\bin\psql.exe";
        & $PG_SQL -d abcd -U abcd -c "\copy \"my-table-name\" from '$(build.artifactstagingdirectory)\dump.csv' with (format csv,header true,delimiter ';')";

With a hope to achieve (this works when ran within the server):

psql -d abcd -U abcd -c "\copy \"my-table-name\" from 'directory\dump.csv' with (format csv,header true,delimiter ';')"

Error:

ERROR:  syntax error at or near "-"
LINE 1: COPY  my-table-name FROM STDIN with (format csv,header true...
                ^

What I tried:

Backtick: & $PG_SQL -d abcd -U abcd -c "\copy "my-table-name" from '$(build.artifactstagingdirectory)\dump.csv' with (format csv,header true,delimiter ';')"; -> gives above mentioned error
Forward slash: & $PG_SQL -d abcd -U abcd -c "\copy \"my-table-name\" from '$(build.artifactstagingdirectory)\dump.csv' with (format csv,header true,delimiter ';')"; -> gives above mentioned error
Double double quotes: & $PG_SQL -d abcd -U abcd -c "\copy ""my-table-name"" from '$(build.artifactstagingdirectory)\dump.csv' with (format csv,header true,delimiter ';')"; -> gives above mentioned error
Pre-storing in variable and then surrounding variable with double quotes: $cpy_cmd = '\copy \"my-table-name\" from D:\a\1\a\my-table-name.csv with (format csv,header true,delimiter \";\")'; & $PG_SQL -d abcd -U abcd -c "$cpy_cmd"; -> works, however here I have to use the static path to the file instead of the cmdlet(is this how I should call it?)

Would this work? `& $PG_SQL -d "host= port=5432 dbname= user= sslmode=require password=" -c \copy "my-table-name" from "directory\dump.csv" with (format csv,header true,delimiter ';');` — GeralexGR, May 17 '22 at 10:27
According to the [docs](https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/resource-name-rules#microsoftstorage) tables names cannot contain hypens. Can you rename the table? — another victim of the mouse, May 24 '22 at 22:09
nope, table cannot be renamed (legacy reasons) + the operations on the table work well everywhere but powershell inside yaml. Ofc. my recommendation will always be not to use hyphens in table names — Igor L., May 25 '22 at 06:58
looks like you might need the backslash for the copy command itself `& $PG_SQL -d abcd -U abcd -c "\copy \""my-table-name\"" from '$(build.artifactstagingdirectory)\dump.csv' with (format csv,header true,delimiter ';')";` — Gregor y, May 26 '22 at 19:51

score 2 · Accepted Answer · answered May 27 '22 at 19:59

In an ideal world, it would be sufficient to satisfy PowerShell's own syntax requirements with respect to embedding " characters inside an expandable (double-quoted) string ("..."), which means escaping them either as `", using PowerShell's general-purpose escape character, the backtick (`), or - less commonly - as "".
However, due to a long-standing bug up to at least PowerShell 7.2.4, you must additionally \-escape such embedded " chars in order for external programs to see them as such.
- See this answer for details, including information about an experimental feature available in preview versions of v7.3, which may fix this problem, possibly on an opt-in basis.

Therefore, first escape for PowerShell's sake, and then prefix with \:

& $PG_SQL -d abcd -U abcd -c "\copy \`"my-table-name\`" from '$(build.artifactstagingdirectory)\dump.csv' with (format csv,header true,delimiter ';')";

Escape double quotes in a command within multiline string concatenation

1 Answers1