0

I'm trying to perform a db query through a docker inline command within a shell script.

myscript.sh:

docker run -it --rm -c "psql -U ${DB_USER} -d ${DB_NAME} -h ${DB_HOST}\
    -c 'select col1, col2 , col3 from table1\
    where table1.col2 = \"matching_text\" order by col1;'"

But I get an odd error:

ERROR:  column "matching_text" does not exist
LINE 1: ...ndow where table1.col2 = "matching_t...

For some reason when I run this, psql thinks the matching_text in my query is referring to a column name. How would I get around this?

Note: Our database is implemented as a psql docker container.

user17255065
  • 11
  • 2
  • Does this answer your question? [How to escape quotes in docker exec bash command](https://stackoverflow.com/questions/67129983/how-to-escape-quotes-in-docker-exec-bash-command) – Nick ODell Oct 26 '21 at 21:56

2 Answers2

1

The Postgres manual explains you need to use single quotes:

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (").

See section 4.1.2.1 of the postgres manual.

Double quotes are for table or column identifiers:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:

UPDATE "my_table" SET "a" = 5;

See section 4.1.1 of the same manual.

Nick ODell
  • 15,465
  • 3
  • 32
  • 66
  • I originally tried with single quotes, but received the same answer. I found my answer after some digging. I'll post a link to it. – user17255065 Oct 26 '21 at 21:50
1

Combination of post here and other post solved this issue:

  1. Need to use single quotes for string query
  2. Use double quotes for -c in psql command (Answer thread)
docker run -it --rm -c "psql -U ${DB_USER} -d ${DB_NAME} -h ${DB_HOST}\
    -c \"select col1, col2 , col3 from table1\
    where table1.col2 = 'matching_text' order by col1;\""
user17255065
  • 11
  • 2