I have something like this:
UPDATE table SET column=REGEXP_REPLACE(column, E'\[(.*)\]$', '');
Everything is ok with this query, but the problem exists when I want to do this from console, like here:
psql -U postgres db -c "UPDATE table SET column=REGEXP_REPLACE(column, E'\\[(.*)\\]$', '');"
I think there is a problem with escaping some characters, but I cannot handle it.
Assuming a Unix-like shell, as an alternative to passing the query with -c and complicated multi-level quoting, it could be passed in its original form in the standard input using heredoc syntax:
$ psql db << EOF
UPDATE table SET column=REGEXP_REPLACE(column, E'\[(.*)\]$', '');
EOF
I found that the correct way is:
UPDATE table SET column=REGEXP_REPLACE(column, E'\\\[(.*)\\\]$', '');
I tried with escaping a $ sign, but this didn't help at all. I understand that one "\" is for bash to escape another one which is needed to db to escape next one.
I think the problem came from the $. Try with :
postgres db -c "UPDATE table SET column=REGEXP_REPLACE(column, E'\\[(.*)\\]\$', '');"
A parameter passed within double quote is evaluated. That's mean it's recognize bash special caracters, like the $, but a parameter within simple quote is not.
