syntax error: unterminated quoted string when using regexp_replace in bash script

Question

I've added some code to the end of a bash script I am using. The code shown here is intended to copy a csv into my postgres table, and then remove brackets, quotes and double quotes from the title column of test_table.

 #copy csv to table

psql -U postgres -d ebay_sold -c "COPY test_table (item_number,title,url,price) FROM '/home/perl_experiments/xmlcsv.txt' (DELIMITER('|'))"

#Remove brackets, then double qotes, then single quotes from title column
    psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '[()]', '', 'g')"
    psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '"', '', 'g')"
    psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '''', '', 'g')"

The copying to the postgres table works fine. The remove brackets, double quotes and single quotes work as expected when applied manually in postgres. However, when I run the bash script, I get:

line 27: syntax error: unterminated quoted string

The error which i'm getting relates to the line

           psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '"', '', 'g')"

As I say this command works fine when performed manually when i'm logged into postgres, does anyone have any idea why i'm getting this error when I run the script in bash?

Answer 1

Your line:

psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '"', '', 'g')"

is the problem. You open a double-quoted string at "UPDATE, but it is closed earlier than you think it is, causing you to attempt to run the following as SQL:

UPDATE test_table SET title = regexp_replace(title, '

This is clearly not valid. You need to escape your double-quote with a backslash:

psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '\"', '', 'g')"