I want to run this SQL on the local and remote host:

select concat_ws('.', table_schema, table_name, column_name) as column_name
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema, table_name, ordinal_position;

The above SQL gets called via psql -c "..."

The psql command on the remote host gets called via ssh remote-host psql ...

Then I want to compare the remote and local result with vimdiff.

Trying to do this with the shell drives me crazy since the quoting is not trivial.

Is there a tool which can help here?

My dream:

compare-with-remote foo@remote-host psql -c "SELECT ..."

The tool should be universal (not just for psql).

Yes, I know this could be done with the shell in one big ugly command line, but I search a clean, simple and readable solution where I don't need to escape quote chars.

I know how to do this interactively with the bash shell in several steps. I have done comparisons like this several time during the last years. Sometimes I call psql sometimes other commands.

Background: The above SQL outputs the tables and the columns in ordinal_position. Related. https://dba.stackexchange.com/questions/120715/compare-order-of-columns-between-two-databases

  • If it's always the same SQL, I'd combine shell scripts: put query.sql on both hosts, then capture output in two (temporary) files which you can diff. No difficult quoting needed, and no extra tools. – Izzy Nov 12 '15 at 12:42
  • @Izzy Yes, that's the solution I use since years. But there could be an easier way. – guettli Nov 12 '15 at 12:47
  • Ah. You didn't write that. Your question reads as if you would pass the SQL command at the prompt, and hence need "difficult quoting" :) – Izzy Nov 12 '15 at 12:49
  • @Izzy I updated the question. I hope it is more clear now. – guettli Nov 12 '15 at 14:23
  • It still looks like you're running something like ssh <remote> psql -c "SELECT …" – while I recommended putting the entire SQL into a file and rather run something like ssh <remote> "psql < script.sql" – which would rid you of all that "quote escaping trouble". – Izzy Nov 12 '15 at 18:00

Your Answer

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

Browse other questions tagged or ask your own question.