ManInMoon February 2016

Why does psql not recognise my single quotes?

$ psql -E --host=xxx --port=yyy --username=chi --dbname=C_DB -c 'DELETE FROM "Stock_Profile" WHERE "Symbol" = 'MSFT'; '

ERROR: column "msft" does not exist LINE 1: DELETE FROM "Stock_Profile" WHERE "Symbol" = MSFT;

How do I show psql that MSFT is a string?

It does not like 'MSFT', \'MSFT\' or ''MSFT''

Answers


kometen February 2016

It's because the single quote before MSFT terminates the string as far as psql is concerned.

As @imsop points out case sensitivity is not preserved when removing double quotes from table names and column names so you can escape the double quotes with backward slash (\) when this is required.

psql -E --host=xxx --port=yyy --username=chi --dbname=C_DB -c "DELETE FROM \"Stock_Profile\" WHERE \"Symbol\" = 'MSFT';"


IMSoP February 2016

The problem you have is that you've run out of types of quote mark to nest; breaking apart, we have:

  1. your shell needs to pass a single string to the psql command; this can be either single quotes or double quotes
  2. your table name is mixed case so needs to be double quoted
  3. your string needs to be single quoted

In the example you give:

psql -E --host=xxx --port=yyy --username=chi --dbname=C_DB -c 'DELETE FROM "Stock_Profile" WHERE "Symbol" = 'MSFT'; '

The shell sees two single-quoted strings:

  • 'DELETE FROM "Stock_Profile" WHERE "Symbol" = '
  • `'; '

So the problem is not in psql, but in the shell itself.

Depending on what shell you are using, single-quoted strings probably don't accept any escapes (so \' doesn't help) but double-quoted strings probably do. You could therefore try using double-quotes on the outer query, and escaping them around the table name:

psql -E --host=xxx --port=yyy --username=chi --dbname=C_DB -c "DELETE FROM \"Stock_Profile\" WHERE \"Symbol\" = 'MSFT'; "

Now the \" won't end the string, so the shell will see this as a single string:

"DELETE FROM \"Stock_Profile\" WHERE \"Symbol\" = 'MSFT'; "

and pass it into psql with the escapes processed, resulting in the desired SQL:

DELETE FROM "Stock_Profile" WHERE "Symbol" = 'MSFT'; 

Post Status

Asked in February 2016
Viewed 2,452 times
Voted 11
Answered 2 times

Search




Leave an answer