1
$ 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''

ManInMoon
  • 6,795
  • 15
  • 70
  • 133

2 Answers2

1

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';
IMSoP
  • 89,526
  • 13
  • 117
  • 169
0

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
  • 89,526
  • 13
  • 117
  • 169
kometen
  • 6,536
  • 6
  • 41
  • 51
  • The double-quotes in this case are not optional, assuming those are the real table and column names, because the uppercase letters will be folded to lower-case and create different names (`stock_profile` vs `Stock_Profile`, `symbol` vs `Symbol`). Postgres is case *sensitive* but also case *folding*; an odd combination, admittedly, and most easily avoided by using all-lowercase identifiers. – IMSoP Feb 08 '16 at 14:05
  • OK, have always called my tables with lowercase names so I missed the part about case sensitivity. I'll edit my answer and credit you. – kometen Feb 08 '16 at 14:09