Home / server / Questions / 415323
Accepted
Chad Harrison
Asked: 2012-08-08 17:32:18 +0800 CST

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

  • 772

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.

postgresql

5 Answers

  1. Best Answer

    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.

    • 45

  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.

    • 7

  3. 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 ;.

    • 4

  4. You can use this syntax in bash (.sh file):

    psql postgres://postgres:postgres@localhost:5432/postgres << SQL
ALTER TABLE table DISABLE TRIGGER all;
TRUNCATE table;
ALTER SEQUENCE table_id_seq RESTART WITH 1;
SQL
    • 4

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

    • 2