CODEWITHSUNDEEP
powershellazure-devopsyaml
Igor L.
Igor L.

Reputation: 3445

Escape double quotes in a command within multiline string concatenation

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:

  1. 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
  2. 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
  3. 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
  4. 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?)

Upvotes: 1

Views: 940

Answers (1)

mklement0
mklement0

Reputation: 437090

  • 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 ';')";

Upvotes: 3

Related Questions