27

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.

2
  • Type it in? Copy and paste? Redirect from a file? Aug 8, 2012 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? Aug 8, 2012 at 1:43

5 Answers 5

49

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.

10

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
7

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.

1
  • Pipes, should have thought about pipes. I still learn. Aug 8, 2012 at 1:44
7

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

3

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .