CODEWITHSUNDEEP
psql
AlwaysLearning
AlwaysLearning

Reputation: 41

Having issues with PSQL automatically lower casing executed query

I am having issues executing the following query. Is there any way to maintain the upper case letters with the psql query? I have tried quotes which does not work and I have tried single quotes, in which case I get a syntax error. NOTE: It appears the columns were create using quotes, fine, but how do I reference quotes from within the command then?)

psql bash CLI

psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "\copy table_name(fileName, time) FROM $OUTPUT_FILE WITH DELIMITER AS ',' NULL 'null'"

Result:

ERROR:  column "filename" of relation "table_name" does not exist

Upvotes: 1

Views: 176

Answers (1)

fphilipe
fphilipe

Reputation: 10056

PostgreSQL treats an identifier such as a table name as lowercase unless you quote it.

You said though that double quotes didn't work. That's probably because you didn't get the quoting right. Quoting in the shell is hard. You have to end your double quote string surrounding the entire query just to start a single quote string that contains the double quote:

psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "\copy table_name("'"fileName"'", time) FROM $OUTPUT_FILE WITH DELIMITER AS ',' NULL 'null'"

You can leverage the fact that psql also accepts the command through standard input, which allows you to write it as follows without worrying about the quotes:

psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME <<SQL
  \copy table_name("fileName", time) FROM $OUTPUT_FILE WITH DELIMITER AS ',' NULL 'null'
SQL

Upvotes: 2

Related Questions