Environment Variables

As with other command-line tools packaged with PostgreSQL, you can forgo specifying your connection settings—host, port, user—by initializing the PGHOST, PGPORT, and PGUSER environment variables. To avoid having to retype the password, you can initialize the variable PGPASSWORD. For more secure access, create a password file as described in PostgreSQL Password File. Since version 9.2 psql accepts two new environment variables:

PSQL_HISTORY

Sets the name of the psql history file that lists all commands executed in the recent past. The default is ~/.psql_history.

PSQLRC

Specifies the location and name of a custom configuration file. Should you decide to create this file, you can place most of your settings in here. At startup, psql will read settings from your configuration file before loading default values, and your file’s settings will override the defaults.

If you omit the parameters when starting psql and failed to initialize environment variables, psql will use the standard defaults.

Interactive versus Noninteractive psql

Run psql interactively by typing psql from your OS command line. Your prompt will transfigure to the psql prompt, signaling that you are now in the interactive psql console. Begin typing in commands. For SQL statements, terminate with a semicolon. If you press Enter without a semicolon, psql will assume that your statement continues to the next line.

Typing \? while in the psql console brings up a list of available commands. For convenience, we’ve reprinted this list in Appendix B, highlighting new additions in the latest versions; see “psql Interactive Commands”. Typing \h followed by the command will bring up the relevant sections of the PostgreSQL documentation pertaining to the command.

To run commands repeatedly or in a sequence, you’re better off creating a script first and then running it using psql noninteractively. At your OS prompt, type psql followed by the name of the script file. Within this script you can mix an unlimited number of SQL and psql commands. Alternatively, you can pass in one or more SQL statements surrounded by double quotes. Noninteractive psql is well-suited for automated tasks. Batch your commands into a file; then schedule it to run at regular intervals using a scheduling daemon like pgAgent, crontab in Linux/Unix, or Windows Scheduler.

Noninteractive psql offers few command-line options because the script file does most of the work. For a listing of all options, see “psql Noninteractive Commands”. To execute a file, use the -f option, as in the following:

psql -f some_script_file

To execute SQL on the fly, use the -c option. Separate multiple statements with a semicolon as in the following:

psql -d postgresql_book -c "DROP TABLE IF EXISTS dross; CREATE SCHEMA staging;"

You can embed interactive commands inside script files. Example 3-1 is the contents of a script named build_stage.psql, which we will use to create a staging table called staging.factfinder_import that is loaded in Example 3-10. The script first generates a CREATE TABLE statement, which it writes to a new file called create_script.sql. It then executes the generated create_script.sql.

\a 
\t
\g create_script.sql
SELECT
    'CREATE TABLE staging.factfinder_import (
        geo_id varchar(255), geo_id2 varchar(255), geo_display varchar(255),' ||
        array_to_string(array_agg('s' ||
        lpad(i::text,2,'0') || ' varchar(255),s' ||
        lpad(i::text,2,'0') || '_perc varchar(255)'),',') ||
    ');'
FROM generate_series(1,51) As i;
\o 
\i create_script.sql 

Since we want the output of our query to be saved as an executable statement, we need to remove the headers by using the \t option (shorthand for --tuples-only) and use the \a option to get rid of the extra breaking elements that psql normally puts in. We then use the \g option to force our query output to be redirected to a file.

We call the \o without file arguments to stop redirection of query results to file.

To execute our generated script, we use the \i followed by the generated script name create_script.sql. The \i is the interactive version of the noninteractive -f option.

To run Example 3-1, we enter the following at an OS prompt:

psql -f build_stage.psql -d postgresql_book

Example 3-1 is an adaptation of an approach we describe in How to Create an N-column Table. As noted in the article, you can perform this without an intermediary file by using the DO command introduced in PostgreSQL 9.0.

psql Customizations

If you spend most of your day in psql, consider tailoring the psql environment to make you more productive. psql reads settings from a configuration file called psqlrc, if present. When psql launches, it searches for this file and runs all commands therein.

On Linux/Unix, the file is customarily named .psqlrc and should be placed in your home directory. On Windows, the file is called psqlrc.conf and should be placed in the %APPDATA%\postgresql folder, which usually resolves to C:\Users\username\AppData\Roaming\postgresql. Don’t worry if you can’t find the file right after installation; you usually need to create it. Any settings in the file will override psql defaults.

Example 3-2 is a glimpse into the contents of a psqlrc file. You can include any psql command.

\pset null 'NULL'
\encoding latin1
\set PROMPT1 '%n@%M:%>%x %/# '
\pset pager always
\timing on
\set qstats92 '
    SELECT usename, datname, left(query,100) || ''...'' As query
    FROM pg_stat_activity WHERE state != ''idle'' ;
'

When you launch psql now, the result of executing the configuration file echoes to the screen:

Null display is "NULL".
Timing is on.
Pager is always used.
psql (9.6beta3)
Type "help" for help.
postgres@localhost:5442 postgresql_book#

Some commands work only on Linux/Unix systems, while others work only on Windows. In either OS, you should use the Linux/Unix−style slash (forward slash) for path. If you want to bypass the configuration file and start psql with all its defaults, start it with the -X option.

You can change settings on the fly while in psql, though the change will only be in effect during your psql session. To remove a configuration variable or set it back to the default, issue the \unset command followed by the setting, as in: \unset qstat92.

When using set, keep in mind that the variable you set is case sensitive. Use all caps to set system options, and lowercase for your own variables. In Example 3-2, PROMPT1 is a system setting for how the psql prompt should appear, whereas qstats92 is a variable initialized as shorthand to display current activities on the PostgreSQL server.

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

postgres@localhost:5442 postgresql_book#

postgres@localhost:5442 postgis_book#

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

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

ROLLBACK;

COMMIT;

\set eav 'EXPLAIN ANALYZE VERBOSE'

:eav SELECT COUNT(*) FROM pg_tables;

\set HISTFILE ~/.psql_history - :DBNAME

psql Gems

In this section, we cover helpful featurettes buried inside the psql documentation.

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

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

\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 |

\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

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)

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

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)

Importing and Exporting Data

psql has a \copy command that lets you import data from and export data to a text file. The tab is the default delimiter, but you can specify others. Newline breaks must separate the rows. For our first example, we downloaded data from US Census Fact Finder covering racial demographics of housing in Massachusetts. You can download the file we use in this example, DEC_10_SF1_QTH1_with_ann.csv, from the PostgreSQL Book Data.

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

\copy sometable FROM somefile.txt DELIMITER '|';

\copy sometable FROM somefile.txt NULL As '';

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

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

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

Basic Reporting

Believe it or not, psql is capable of producing basic HTML reports. Try the following and check out the generated output, shown in Figure 3-1.

psql -d postgresql_book -H -c "
SELECT category, COUNT(*) As num_per_cat
FROM pg_settings
WHERE category LIKE '%Query%'
GROUP BY category
ORDER BY category;
" -o test.html

Figure 3-1. Minimalist HTML report

Not too shabby. But the command outputs only an HTML table, not a fully qualified HTML document. To create a meatier report, compose a script, as shown in Example 3-14.

\o settings_report.html 
\T 'cellspacing=0 cellpadding=0' 
\qecho '<html><head><style>H2{color:maroon}</style>' 
\qecho '<title>PostgreSQL Settings</title></head><body>'
\qecho '<table><tr valign=''top''><td><h2>Planner Settings</h2>'
\x on 
\t on 
\pset format html 
SELECT category, 
string_agg(name || '=' || setting, E'\n' ORDER BY name) As settings 
FROM pg_settings
WHERE category LIKE '%Planner%'
GROUP BY category
ORDER BY category;
\H
\qecho '</td><td><h2>File Locations</h2>'
\x off 
\t on
\pset format html
SELECT name, setting FROM pg_settings WHERE category = 'File Locations' 
ORDER BY name;
\qecho '<h2>Memory Settings</h2>'
SELECT name, setting, unit FROM pg_settings WHERE category ILIKE '%memory%' 
ORDER BY name;
\qecho '</td></tr></table>'
\qecho '</body></html>'
\o

Redirects query output to a file.

CSS table settings for query output.

Appends additional HTML.

Expand mode. Repeats the column headers for each row and outputs each column of each row as a separate row.

Forces the queries to output as an HTML table.

string_agg(), introduced in PostgreSQL 9.0, concatenates all properties in the same category into a single column.

Turns off expand mode. The second and third queries should output one row per table row.

Toggles tuples mode. When on, column headers and row counts are omitted.

Example 3-14 demonstrates that by interspersing SQL and psql commands, you can create a comprehensive tabular report replete with subreports. Run Example 3-14 by connecting interactively with psql and executing \i settings_report.psql. Alternatively, run psql noninteractively by executing psql -f settings_report.psql from your OS command line. The output generated by settings_report.html is shown in Figure 3-2.

Figure 3-2. Advanced HTML report

As demonstrated, composing psql scripts lets you show output from many queries within a single report. Further, after you write a script, you can schedule its execution in the future, and at fixed intervals. Use a daemon like pgAgent, crontab, or Windows Scheduler.