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

Question

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.

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 08 '12 at 01:43

score 48 · Accepted Answer · edited Sep 05 '19 at 12:45

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.

score 9 · Answer 2 · edited Jul 30 '23 at 06:26

9

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

psql postgres://postgres:postgres@localhost:5432/postgres << SQL
CREATE TABLE "public"."links" (
  id bigint PRIMARY KEY,
  from_id bigint DEFAULT 0,
  to_id bigint DEFAULT 0,
  type_id bigint NOT NULL
);
SQL

edited Jul 30 '23 at 06:26

Community

1

answered May 13 '21 at 14:31

Konard

191
1
4

score 7 · Answer 3 · edited Sep 05 '19 at 18:47

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 08 '12 at 01:44 — Chad Harrison, Aug 08 '12 at 01:44

score 6 · Answer 4 · answered Apr 15 '20 at 08:05

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

score 2 · Answer 5 · answered Apr 23 '18 at 05:44

2

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

answered Apr 23 '18 at 05:44

Nam G VU

287
2
5
15

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

5 Answers5