psql column name not exist when running through bash script

Question

I'm trying to run several number of sql statement in bash script. I tried to run the following statement on psql, it works fine. However, when I run the exact statement on script, it says the column does not exist.

The statement

\copy (SELECT * FROM table_a h JOIN table_b b ON h."IDX" = b."IDX" WHERE b."XXX" BETWEEN 0 AND 100) to 'D:\$path\onetry1.csv' with csv HEADER;

I am aware in Postgres, it is required to use double quotes on uppercase column names. I did that.

This is my bash script:

#! /bin/sh

db="usm"

PGPASSWORD=XXX123 psql -h localhost -U postgres -d $db -c "\copy (SELECT * FROM table_a h JOIN table_b b ON h."IDX" = b."IDX" WHERE b."XXX" BETWEEN 0 AND 500) to '$path\onetry2.csv' with csv HEADER;"

The error I'm getting:

ERROR: column h.idx does not exist LINE 1: ...M table_a h JOIN table_b b ON h.IDX = b....

Another good example why using quoted identifiers is simply not worth the hassle they create — , Nov 09 '19 at 08:13

score 1 · Answer 1 · answered Nov 09 '19 at 05:15

Your shell script doesn't send the double quotes. You have this:

"\copy (SELECT * FROM table_a h JOIN table_b b ON h."IDX" = b."IDX" WHERE b."XXX" BETWEEN 0 AND 500) to '$path\onetry2.csv' with csv HEADER;"

it essentially works as a concatenation of the following strings

"\copy (SELECT * FROM table_a h JOIN table_b b ON h."
IDX
" = b."
IDX
" WHERE b."
XXX
" BETWEEN 0 AND 500) to '$path\onetry2.csv' with csv HEADER;"

but PostgreSQL never gets to see the double quotes. Use backslash escape the nested double quotes:

"\copy (SELECT * FROM table_a h JOIN table_b b ON h.\"IDX\" = b.\"IDX\" WHERE b.\"XXX\" BETWEEN 0 AND 500) to '$path\onetry2.csv' with csv HEADER;"

also, prefer to avoid forcing all upper case or any specific case for that matter. You can still type upper case if you want. I use quoted column names only to format final queries prettier.

I found the solution, I should be adding escape "\"GPH"\" and it works fine. Thank you — dee, Nov 09 '19 at 05:28

score 0 · Answer 2 · answered Nov 09 '19 at 06:11

For cases where programs takes input which may conflict with the shell process (e.g., quotes, double quotes, and various substitutions), it is usually better to use one of the other quoting options:

If the programs can process STDIN, here documents (<<word) can be used.
If the programs only works with command line parameters, here document can be inlined
Consider using variables to simplify quoting.

I believe psql also takes stdin as input, so here documents can work (removing -c). Making it easier to format the SQL for readibility.

PGPASSWORD=XXX123 psql -h localhost -U postgres -d $db <<SQL
\copy (
   SELECT *
   FROM table_a h
   JOIN table_b b ON h."IDX" = b."IDX"
   WHERE b."XXX" BETWEEN 0 AND 500)
   to '$path\onetry2.csv' with csv HEADER;
SQL

If the programs takes only command line arguments, here strings can be used (note that this is bash only feature, not POSIX).

PGPASSWORD=XXX123 psql -h localhost -U postgres -d $db -c "$(cat <<SQL
\copy (
   SELECT *
   FROM table_a h
   JOIN table_b b ON h."IDX" = b."IDX"
   WHERE b."XXX" BETWEEN 0 AND 500)
   to '$path\onetry2.csv' with csv HEADER;
SQL
)

If the command is constructed from variables (or can be stored in variables), it can eliminate the complex quoting.

V=$(cat <<SQL
\copy (
   SELECT *
   ...
   to '$path\onetry2.csv' with csv HEADER;
SQL
)
PGPASSWORD=XXX123 psql -h localhost -U postgres -d $db -c "$v"

psql column name not exist when running through bash script

2 Answers2