Using the psql query and scripting tool

psql is the query tool supplied as a part of the core distribution of PostgreSQL, so it is available in all environments and works similarly in all of them. This makes it an ideal choice for developing portable applications and techniques.

psql provides features for use as both an interactive query tool and as a scripting tool.

Getting ready

From here on, we will assume that the psql command alone is enough to allow you access to the PostgreSQL server. This assumes that all your connection parameters are defaults, or that you have set environment variables appropriately, as previously explained in the Enabling access for remote/network users recipe.

Written in full, the connection parameters will be either of these options:

psql -h myhost -p 5432 -d mydb -U myuser
psql postgresql://myuser@myhost:5432/mydb

The default value for the port (-p) is 5432. By default, mydb and myuser are both identical to the operating system’s username. The default myhost on Windows is localhost, while on Unix, we use the default directory for Unix socket connections. The location of such directories varies across distributions and is set at compile time. However, note that you don’t actually need to know its value because, on local connections, both the server and the client are normally compiled together, so they use the same default.

How to do it…

The command that executes a single SQL command and prints the output is the easiest, as shown here:

$ psql -c "SELECT current_time"
     timetz
-----------------
18:48:32.484+01
(1 row)

The -c command is non-interactive. If we want to execute multiple commands, we can write those commands in a text file and then execute them using the -f option. This command loads a very small and simple set of examples:

$ psql -f examples.sql

The contents of the examples.sql file are as follows:

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
DROP SCHEMA IF EXISTS myschema CASCADE;
CREATE SCHEMA myschema;
SET default_tablespace = '';
SET default_table_access_method = heap;
SET search_path = myschema;
CREATE TABLE mytable (
    id integer PRIMARY KEY,
    col1 text
);
CREATE TABLE mytable2 (
    id integer,
    fid integer REFERENCES mytable(id),
    col2 timestamp with time zone DEFAULT clock_timestamp(),
    PRIMARY KEY (id, fid)
);
COPY mytable (id, col1) FROM stdin;
1	Ananas
2	Banana
3	Cucumber
4	Dasheen
5	Endive
\.
COPY mytable2 (id, fid, col2) FROM stdin;
1001	1	2023-11-15 18:49:14.84806+01
1001	2	2023-11-15 18:49:14.848334+01
1002	5	2023-11-15 18:49:14.848344+01
\.

The above command produces the following output when successful, which is a list of command tags that show the command that was executed, and how many rows were affected:

SET
SET
SET
SET
SET
SET
DROP SCHEMA
CREATE SCHEMA
SET
SET
SET
CREATE TABLE
CREATE TABLE
COPY 5
COPY 3

The examples.sql script is very similar to a dump file produced by PostgreSQL backup tools, so this type of file and the output it produces are very common; in fact, we produced it by creating a dump file and then removing some parts that were not needed by this example.

When a command is executed successfully, PostgreSQL outputs a command tag equal to the name of that command; this is how the preceding output was produced.

The psql tool can also be used with both the -c and -f modes together; each one can be used multiple times. In this case, it will execute all the commands consecutively:

$ psql -c "SELECT current_time" –f examples.sql -c "SELECT current_time"
     timetz
-----------------
18:52:15.287+01
(1 row)
   ...output removed for clarity...
     timetz
-----------------
18:58:23.554+01
(1 row)

The psql tool can also be used in interactive mode, which is the default, so it requires no option:

$ psql
postgres=#

The first interactive command you’ll need is the following:

postgres=# help

You can then enter SQL or other commands. The following is the last interactive command you’ll need:

postgres=# \quit

Unfortunately, you cannot type quit on its own, nor can you type \exit or other options. Sorry – it’s just \quit, or \q for short!

How it works…

In psql, you can enter the following two types of command:

• psql meta-commands
• SQL

A meta-command is a command for the psql client, which may (or may not) send SQL to the database server, depending on what it actually does, whereas an SQL command is always sent to the database server. An example of a meta-command is \q, which tells the client to disconnect. All lines that begin with \ (a backslash) as the first non-blank character are presumed to be meta-commands of some kind.

If it isn’t a meta-command, it’s SQL, in which case psql keeps reading SQL until we find a semicolon, so we can spread SQL across many lines and format it any way we find convenient.

The help command is the only exception. We provide this for people who are completely lost, which is a good thought; so let’s start from there ourselves.

There are two types of help commands, which are as follows:

• \?: This provides help on psql meta-commands.
• \h: This provides help on specific SQL commands.

Consider the following snippet as an example:

postgres=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
    [ USING usinglist ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ AS output_name ] [,]]

I find this a great way to discover and remember options and syntax. You’ll also appreciate having the ability to scroll back through the previous command history if your terminal allows it.

You’ll get a lot of benefits from tab completion, which will fill in the next part of the syntax when you press the Tab key. This also works for object names, so you can type in just the first few letters and then press Tab; all the options will be displayed. Thus, you can type in just enough letters to make the object name unique and then hit Tab to get the rest of the name.

Like most programming languages, SQL also supports comments. One-line comments begin with two dashes, as follows:

-- This is a single-line comment

Multiline comments are similar to those in C and Java:

/*
Multiline comment
line 2
line 3
*/

You’ll probably agree that psql looks a little daunting at first, with strange backslash commands. I do hope you’ll take a few moments to understand the interface and keep digging for more information. The psql tool is one of the most surprising parts of PostgreSQL, and it is incredibly useful for database administration tasks when used alongside other tools.

There’s more…

psql works across releases and works well with older versions. It may not work at all with newer server versions, so use the latest client level of the server you are accessing.

See also

Check out some other useful features of psql, which are as follows:

• Informational metacommands, such as \d, \dn, and more
• Formatting, for output, such as \x
• Execution timing using the \timing command
• Input/output and editing commands, such as \copy, \i, and \o
• Automatic startup files, such as .psqlrc
• Substitutable parameters (variables), such as \set and \unset
• Access to the OS command line using \!
• Crosstab views with \crosstabview
• Conditional execution, such as \if, \elif, \else, and \endif