CODEWITHSUNDEEP
powershellpsql
Marius
Marius

Reputation: 9674

Escape quotes in psql command when running from PowerShell

& "psql" "-d" "yourdb" "-h" "localhost" "-p" "5432" "-U" "youruser" "-c" "select foo from bar where data @> `'{ `"key`" : `"value`" }`';"

ERROR: invalid input syntax for type json
LINE 1: ...foo from bar where data @> '{ key...

How do I escape that command string so that psql will not throw up?

Upvotes: 2

Views: 1904

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

The recommended approach is to use the Npgsql or psqlODBC driver, since they have numerous advantages when it comes to the handling of basically everything (connections, arguments, results, errors, …).

However, if you insist on using the commandline client you can fix the syntax error by adding backslashes to escape the nested double quotes for the execution of the external command (the backticks are just to escape them for the PowerShell parser):

"SELECT foo FROM bar WHERE data @> '{ \`"key\`" : \`"value\`" }';"
#                                     ^     ^     ^       ^

The single quotes don't need escaping.

I would also recommend using splatting for passing the arguments:

$params = '-d', 'yourdb',
          '-h', 'localhost',
          '-p', 5432,
          '-U', 'youruser',
          '-c', "SELECT foo FROM bar WHERE data @> '{\`"key\`":\`"value\`"}';"

& 'psql.exe' @params

Note that you must invoke psql.exe with its (absolute or relative) path if the directory containing the executable is not in your PATH environment variable.

Upvotes: 7

Related Questions