Problem with quotations when executing psql query over ssh

Question

I would like to execute a psql query through ssh directly from local machine.

In remote machine, the command works fine:

psql -U USER -d DATABASE -c "select A,B,C from TABLE where A='string1';"

Now, when using:

ssh user@host "psql -U USER -d DATABASE -c "select A,B,C from TABLE where A='string1';""

I get errors such as:

psql: warning: extra command-line argument "A,B,C" ignored
psql: warning: extra command-line argument "from" ignored
psql: warning: extra command-line argument "TABLE" ignored
psql: warning: extra command-line argument "where" ignored
psql: warning: extra command-line argument "A=string1" ignored

I understand that the problem is due to the quotes as the following command in the local machine works fine:

ssh user@host "psql -U USER -d DATABASE -c '\l'"

How do I solve this?

Answer 1

Taking cues from this answer, the following command works:

ssh user@host 'psql -U USER -d DATABASE -c "select A,B,C from TABLE where A='"'"'string1'"'"';'