Why I get an error when I try to execute a command remotely?

I have a problem about execute command remotely via SSH. I am trying below.

ssh xx.xx.xx.xx "psql -U qradar -c "select count(id) from offense_view where to_timestamp(start_time/1000) > NOW() - interval '180 minutes'"

It gives an error like:

Pseudo-terminal will not be allocated because stdin is not a terminal.
ERROR:  syntax error at or near "180"
LINE 1: ... to_timestamp(start_time/1000) > NOW() - interval 180 minute...

Answer

The problem is that you’re using double quotes to delimit the argument to ssh and also the argument to psql inside the command. This is causing your strings to be parsed incorrectly. You’re also missing the ending double quote for the psql command.

Nesting quotes is tricky in shell, and it’s even harder when you’re using ssh. It’s easier if you use a here-doc.

ssh xx.xx.xx.xx <<EOF
psql -U qradar -c "select count(id) from offense_view where to_timestamp(start_time/1000) > NOW() - interval '180 minutes'"
EOF