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

Say I need hand write some queries in the console, what's the most efficient way of executing multiline queries like CREATE TABLE statements?

I am used to using Microsoft Management Studio, but I now find myself having to learn about PostgreSQL on the fly.

• Type it in? Copy and paste? Redirect from a file? – Michael Hampton Aug 8 '12 at 1:39
• Yes I mean type it in with my fingers :P. What I'm getting at is that the postgres # prompt appears only enter one line at a time which is great for things like CREATE DATABASE or ALTER USER. How would I be able to nicely format my text before executing? – Chad Harrison Aug 8 '12 at 1:43

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.

• Pipes, should have thought about pipes. I still learn. – Chad Harrison Aug 8 '12 at 1:44

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.

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

Obvious, but if anyone is not aware, you can write multi-line statements directly in the psql prompt:

psql -h localhost -p 5432 -U postgres public
public=# SELECT
public=# *
public=# FROM
public=# mytable
public=# LIMIT 1;


Note the trailing ; character - any SQL command will only be executed once this character has been issued. So the easiest way is to simply copy & paste multi-line SQL commands with a trailing ;.