Custom Prompts

If you spend your waking hours playing with psql connecting to multiple servers and databases, customizing your prompt to display the connected server and database will enhance your situational awareness and possibly avoid disaster. Here’s a simple way to set a highly informational prompt:

set PROMPT1 '%n@%M:%>%x %/# '

This includes whom we are logged in as (%n), the host server (%M), the port (%>), the transaction status (%x), and the database (%/). This is probably overkill, so economize as you see fit. The complete listing of prompt symbols is documented in the psql Reference Guide.

When we connect with psql to our database, our enhanced prompt looks like:

postgres@localhost:5442 postgresql_book#

Should we switch to another database using connect postgis_book, our prompt changes to:

postgres@localhost:5442 postgis_book#

Timing Executions

You may find it instructive to have psql output the time it took for each query to execute. Use the iming command to toggle it on and off.

When enabled, each query you run will report the duration at the end. For example, with iming on, executing SELECT COUNT(*) FROM pg_tables; outputs:

count
--------
73
(1 row)
Time: 18.650 ms

Autocommit Commands

By default, autocommit is on, meaning any SQL command you issue that changes data will immediately commit. Each command is its own transaction and is irreversible. If you are running a large batch of precarious updates, you may want a safety net. Start by turning off autocommit: set AUTOCOMMIT off. Now, you have the option to roll back your statements:

UPDATE census.facts SET short_name = 'This is a mistake.';

To undo the update, run:

ROLLBACK;

To make the update permanent, run:

COMMIT;

Shortcuts

You can use the set command to create useful keyboard shortcuts. Store universally applicable shortcuts in your psqlrc file. For example, if you use EXPLAIN ANALYZE VERBOSE once every 10 minutes, create a shortcut as follows:

set eav 'EXPLAIN ANALYZE VERBOSE'

Now, all you have to type is :eav (the colon resolves the variable):

:eav SELECT COUNT(*) FROM pg_tables;

You can even save entire queries as shortcuts as we did in Example 3-2. Use lowercase to name your shortcuts to distinguish them from system settings.

Retrieving Prior Commands

As with many command-line tools, you can use the up arrows in psql to recall commands. The HISTSIZE variable determines the number of previous commands that you can recall. For example, set HISTSIZE 10 lets you recover the past 10 commands.

If you spent time building and testing a difficult query or performing a series of important updates, you may want to have the history of commands piped into separate files for perusal later:

set HISTFILE ~/.psql_history - :DBNAME

Executing Shell Commands

In psql, you can call out to the OS shell with the ! command. Let’s say you’re on Windows and need a directory listing. Instead of exiting psql or opening another window, you can just type ! dir at the psql prompt.

Watching Statements

The watch command has been in psql since PostgreSQL 9.3. Use it to repeatedly run an SQL statement at fixed intervals so you can monitor the output. For example, suppose you want to keep tabs on queries that have yet to complete. Tag the watch command to the end of the query as shown in Example 3-3.

SELECT datname, query
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid();
watch 10

Although watch is primarily for monitoring query output, you can use it to execute statements at fixed intervals. In Example 3-4, we first create a table using bulk insert syntax and then log activity every five seconds after. Only the last statement that does the insert is repeated every five seconds.

SELECT * INTO log_activity
FROM pg_stat_activity; 
INSERT INTO log_activity
SELECT * FROM pg_stat_activity; watch 5 

Create table and do first insert.

Insert every five seconds.

To kill a watch, use CTRL-X CTRL-C.

Retrieving Details of Database Objects

Various psql describe commands list database objects along with details. Example 3-5 demonstrates how to list all tables and their sizes on disk in the pg_catalog schema that begins with the letters pg_t.

dt+ pg_catalog.pg_t*

Schema     | Name             | Type  | Owner    | Size   | Description
-----------+------------------+-------+----------+--------+------------
pg_catalog | pg_tablespace    | table | postgres | 40 kB  |
pg_catalog | pg_trigger       | table | postgres | 16 kB  |
pg_catalog | pg_ts_config     | table | postgres | 40 kB  |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB  |
pg_catalog | pg_ts_dict       | table | postgres | 40 kB  |
pg_catalog | pg_ts_parser     | table | postgres | 40 kB  |
pg_catalog | pg_ts_template   | table | postgres | 40 kB  |
pg_catalog | pg_type          | table | postgres | 112 kB |

If you need further detail on a particular object, use the d+ command as shown in Example 3-6.

d+ pg_ts_dict

Table "pg_catalog.pg_ts_dict"
Column         | Type | Modifiers | Storage  | Stats target | Description
---------------+------+-----------+----------+--------------+------------
dictname       | name | not null  | plain    |              |
dictnamespace  | oid  | not null  | plain    |              |
dictowner      | oid  | not null  | plain    |              |
dicttemplate   | oid  | not null  | plain    |              |
dictinitoption | text |           | extended |              |
Indexes:
"pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
"pg_ts_dict_oid_index" UNIQUE, btree (oid)
Has OIDs: yes

Crosstabs

New in PostgreSQL 9.6 psql is the crosstabview command, which greatly simplifies crosstab queries. This labor-saving command is available only in the psql enviroment. We’ll illustrate with an example in Example 3-7, following it with an explanation.

SELECT student, subject, AVG(score)::numeric(5,2) As avg_score
FROM test_scores
GROUP BY student, subject
ORDER BY student, subject
crosstabview student subject avg_score

 student | algebra | calculus | chemistry | physics | scheme
---------+---------+----------+-----------+---------+--------
 alex    |   74.00 |    73.50 |     82.00 |   81.00 |
 leo     |   82.00 |    65.50 |     75.50 |   72.00 |
 regina  |   72.50 |    64.50 |     73.50 |   84.00 |  90.00
 sonia   |   76.50 |    67.50 |     84.00 |   72.00 |
(4 rows)

The crosstabview immediately follows the query you want to cross tabulate. The crosstabview should list three columns selected by the query, with an optional fourth column to control sorting. The cross tabulation outputs a table where the first column serves as a row header, the second column as a column header, and the last as the value that goes in each cell. You can also omit the column names from the crosstabview command, in which case the SELECT statement must request exactly three columns used in order for the cross tabulation.

In Example 3-7, student is the row header and subject is the column header. The average score column provides the entry for each pivoted cell. Should our data contain a missing student-subject pair, the corresponding cell would be null. We specified all the columns in the crosstabview command, but we could have omitted them because they are in our SELECT in the right order.

Dynamic SQL Execution

Suppose you wanted to construct SQL statements to run based on the output of a query. In prior versions of PostgreSQL, you would build the SQL, output it to a file, then execute the file. Alternatively you could use the DO construct, which could be unwieldy in psql for long SQL statements. Starting with PostgreSQL 9.6, you can execute generated SQL in a single step with the new gexec command, which iterates through each cell of your query and executes the SQL therein. Iteration is first by row then by column. It’s not yet smart enough to discern whether each cell contains a legitimate SQL. gexec is also oblivious to the result of the SQL execution. Should the SQL within a particular cell throw an error, gexec merrily treads along. However, it skips over nulls. Example 3-8 creates two tables and inserts one row in each table using the gexec command.

SELECT
    'CREATE TABLE ' || person.name || '( a integer, b integer)' As create,
    'INSERT INTO ' || person.name || ' VALUES(1,2) ' AS insert
 FROM (VALUES ('leo'),('regina')) AS person (name) gexec

CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 1

In the next example we use gexec to obtain metadata by querying information_schema.

SELECT
'SELECT ' || quote_literal(table_name) || ' AS table_name,
COUNT(*) As count FROM ' || quote_ident(table_name) AS cnt_q
FROM information_schema.tables
WHERE table_name IN ('leo','regina') gexec

table_name | count
-----------+------
leo        | 1
(1 row)

table_name | count
-----------+------
 regina    | 1
(1 row)

psql Import

Our usual sequence in loading denormalized or unfamiliar data is to create a staging schema to accept the incoming data. We then write explorative queries to get a sense of what we have on our hands. Finally, we distribute the data into various normalized production tables and delete the staging schema.

Before bringing the data into PostgreSQL, you must first create a table to store the incoming data. The data must match the file both in the number of columns and in data types. This could be an annoying extra step for a well-formed file, but it does obviate the need for psql to guess at data types.

psql processes the entire import as a single transaction; if it encounters any errors in the data, the entire import fails. If you’re unsure about the data contained in the file, we recommend setting up the table with the most accommodating data types and then recasting them later if necessary. For example, if you can’t be sure that a column will have just numeric values, make it character varying to get the data in for inspection and then recast it later.

Example 3-10 loads data into the table we created in Example 3-1. Launch psql from the command line and run the commands in Example 3-10.

connect postgresql_book
cd /postgresql_book/ch03
copy staging.factfinder_import FROM DEC_10_SF1_QTH1_with_ann.csv CSV

In Example 3-10, we launch interactive psql, connect to our database, use cd to change the current directory to the folder containing our file, and import our data using the copy command. Because the default delimiter is a tab, we augment our statement with CSV to tell psql that our data is comma-separated instead.

If your file has nonstandard delimiters such as pipes, indicate the delimiter as follows:

copy sometable FROM somefile.txt DELIMITER '|';

During import, you can replace null values with something of your own choosing by adding a NULL AS, as in the following:

copy sometable FROM somefile.txt NULL As '';

psql Export

Exporting data is even easier than importing. You can even export selected rows from a table. Use the psql copy command to export. Example 3-11 demonstrates how to export the data we just loaded back to a tab-delimited file.

connect postgresql_book
copy (SELECT * FROM staging.factfinder_import  WHERE s01 ~ E'^[0-9]+' ) 
TO '/test.tab'
WITH DELIMITER E'	' CSV HEADER

The default behavior of exporting data without qualifications is to export to a tab-delimited file. However, the tab-delimited format does not export header columns. You can use the HEADER option only with the comma-delimited format (see Example 3-12).

connect postgresql_book
copy staging.factfinder_import TO '/test.csv'
WITH CSV HEADER QUOTE '"' FORCE QUOTE *

FORCE QUOTE * double quotes all columns. For clarity, we specified the quoting character even though psql defaults to double quotes.

Copying from or to Program

Since PostgreSQL 9.3, psql can fetch data from the output of command-line programs such as curl, ls, and wget, and dump the data into a table. Example 3-13 imports a directory listing using a dir command.

connect postgresql_book
CREATE TABLE dir_list (filename text);
copy dir_list FROM PROGRAM 'dir C:projects /b'

Hubert Lubaczewski has more examples of using copy. Visit Depesz: Piping copy to from an external program.