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.

CodePudding user response：

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.

CodePudding user response：

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;\""