0

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?

|improve this question
3

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')"
|improve this answer
  • HI, sorry, when I copied my script for some reason the end of that line didn't copy onto this webpage, i've updated my question so now it reflects my script, there is a double quote at the end of that line. – neilH May 8 '16 at 19:33
  • Yes, but that's no help.  bash sees "UPDATE test_table SET title = regexp_replace(title, '" as a string, the ', '', 'g') after it as confusing noise, and the " at the end of the line as the beginning of a second quoted string (on that line).  No, wait; that's wrong.  It sees ', ' as a second string, ', ' as a third, and then the ' after the g starts a fourth string. – G-Man May 8 '16 at 20:12
  • Not true. Test with var="UPDATE test_table SET title = regexp_replace(title, '\"', '', 'g')"; echo $var That is exactly what the OP's question was regarding, and what my answer resolves: escaping the double-quote so that bash doesn't parse it, allowing it to make it through to psql. – DopeGhoti May 8 '16 at 23:06
  • ah, sorry i'll try your edited suggestion now and get back to you – neilH May 9 '16 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.