psql cli

psql is the primary tool to work with and manage a PostgreSQL database server.

Setting the prompt

The default psql prompt shows the database name, in this fashion:

psql default prompt

name_of_the_current_database=#

Sometimes we just prefer a simpler, and shorter prompt.

It is possible to set the prompt from the shell command line:

$ psql -h 172.19.0.2 -U devel --set='PROMPT1=SQL> '
Password for user devel:
psql (15.4, server 14.8 (Debian 14.8-1.pgdg120+1))
Type "help" for help.

SQL> \c mycms_devel
psql (15.4, server 14.8 (Debian 14.8-1.pgdg120+1))
You are now connected to database "mycms_devel" as user "devel".
SQL>

If you are already inside a psql session, use \set:

mycms_devel=# \set PROMPT1 'SQL> '
SQL>

In both cases, not how the name of the current database is not shown any longer, and we see the simpler and shorter ‘SQL> ’ prompt.

Other ideas is to use simply ‘> `’ or ‘» `’

Query from a bash here document

Suppose we need to run a query on psql with --command:

$ POSTGRES_PASSWORD=h4ck3r \
    psql \
    --host=127.0.0.1 \
    --port=5432 \
    --user=devy \
    --command='SELECT 1 AS num';

But imagine the query would be long and take multiple lines. In that case, we could use cat with a Bash’s Here Document:

query=$(cat << EOF
  SELECT
      id
    , first_name
    , last_name
    , email
  FROM users
  WHERE id = 1;
EOF
)

POSTGRES_PASSWORD=h4ck3r \
  psql \
  --host=127.0.0.1 \
  --port=5432 \
  --user=devy \
  --command="$query";

And the result would be something like this:

 id | first_name | last_name |       email
----+------------+-----------+--------------------
  1 |   Aayla    |  Secura   | aayla@theforce.org