What's a convenient way to execute multiline queries in postgres using ssh?

Solution 1:

The following will take you to PostgreSQL's interactive terminal:

$ psql <your database name>

Then enter \e (or \edit) to open an editor (vi is default):

# \e

Write some query:

select now();

Finally, save and quit your editor (e.g. :wq in vi), and psql will run the query you just wrote.

To set a different editor, such as vim or nano, set one of the following environment variables: PSQL_EDITOR, EDITOR, VISUAL.

For more information, see https://www.postgresql.org/docs/current/app-psql.html and search for \e.

Solution 2:

There is really no such thing as a "multi-line query" - as far as PostgreSQL cares, all that whitespace and those newlines are meaningless (unless they occur inside a string or we're talking about tabs & such in a COPY statement or a few other special places).

Writing queries "on the console" over SSH is usually done with the psql tool, which is the Postgres interactive terminal (see the docs for more info).

You can pass text files containing queries to psql using the -f option.

You can also pass queries to psql on the command line (quoted as appropriate for your shell), or pipe them in via STDIN (the standard input stream) - see the -c option.

---

Solution 3:

You can write the query to a temp file and execute it with psql -f /path/to/temp/file