NAME

pspg - Postgres Pager

Everybody who uses psql also uses the less pager. Which, while it works well, has no special support for tabular data. I found a few projects, but none was good enough for this purpose. Thus I decided to write a small specialized pager to use as a psql pager.

This pager can be used from the following command line clients, too:

• mysql
• sqlite
• pgcli https://github.com/dbcli/pgcli/
• monetdb
• Trino (formerly Presto SQL) https://trino.io/
• usql https://github.com/xo/usql/
• sqlcl https://github.com/okbob/pspg/#sqlcl (for oracle)
• nushell https://www.nushell.sh/

Main target

• ability to freeze the first few rows, or first few columns
• ability to sort data by the specified numeric column
• ability to use fancy themes - like mcview or FoxPro - http://okbob.blogspot.com/2019/12/pspg-themes-what-you-use-it.html
• mouse is supported and used
• ability to copy a selected range to the clipboard

Installation and basic configuration

The pspg can be simply installed from Debian (Ubuntu) repositories. RedHat (Fedora) repositories contains pspg too:

# Debian (Ubuntu)
sudo apt-get install pspg
# RedHat (Fedora)
sudo dnf install pspg

Basic configuration is very simple - just set system environment variable PSQL_PAGER:

export PSQL_PAGER='pspg'

or with some common options (-b means blackwhite theme, -X preserve content after exit):

export PSQL_PAGER='pspg -X -b'

Attention: options used in command line has higher priority than options specified in configuration file (~/.pspgconf).

Native installation on MS Windows is not supported, but pspg works well inside wsl2. Inside wsl2 environment, the installation is same like on used Linux system.

Installation on macOS/homebrew is simple by brew install pspg.

Video presentation

https://www.youtube.com/watch?v=JyxuEkoYDQk

Screenshots

Options

[pavel@localhost ~]$ pspg --help
pspg is a Unix pager designed for table browsing.
Usage:


  pspg [OPTION] [file]
General options:


  --about                  about authors


  --help                   show this help


  -V, --version            show version


  --info                   show info about libraries and system


  --direct-color           force direct-color terminal mode


  -f, --file=FILE          open file


  -F, --quit-if-one-screen


                           quit if content is one screen


  --clipboard-app=NUM      specify app used by copy to clipboard (1, 2, 3, 4)


  --esc-delay=NUM          specify escape delay in ms (-1 inf, 0 not used, )


  --interactive            force interactive mode


  --ignore_file_suffix     don't try to deduce format from file suffix


  --ni                     not interactive mode (only for csv and query)


  --no-watch-file          don't watch inotify event of file


  --no-mouse               don't use own mouse handling


  --no-progressive-load    don't use progressive data load


  --no-sigint-search-reset


                           without reset searching on sigint (CTRL C)


  --no-sleep               without waits against flickering


  --no_xterm_mouse_mode    don't use optional xterm mouse mode


  --only-for-tables        use std pager when content is not table


  --on-sigint-exit         exit on sigint(CTRL C or Escape)


  --pgcli-fix              try to fix some pgcli related issues


  --querystream            read queries from stream forever


  --quit-on-f3             exit on F3 like mc viewers


  --rr=ROWNUM              rows reserved for specific purposes


  --stream                 read input forever


  -X, --reprint-on-exit    preserve content after exit
Output format options:


  -a, --ascii              force ascii


  -b, --blackwhite         black-white style


  -s, --style=N            set color style number (0..22)


  --bold-labels            row, column labels use bold font


  --bold-cursor            cursor use bold font


  --border                 type of borders (0..2)


  --double-header          header separator uses double lines


  --force-uniborder        replace ascii borders by unicode borders


  --highlight-odd-rec      highlights odd records (when it is supported by style)


  --hide-header-line       hides header line (between column names and data)


  --ignore-short-rows        rows with wrong column numbers are ignored


  --null=STRING            STRING used instead NULL
Searching options


  -g --hlite-search, -G --HILITE-SEARCH


                           don't highlight lines for searches


  -i --ignore-case         ignore case in searches that do not contain uppercase


  -I --IGNORE-CASE         ignore case in all searches
Interface options:


  -c, --freezecols=N       freeze N columns (0..9)


  --less-status-bar        status bar like less pager


  --line-numbers           show line number column


  --menu-always            show top bar menu every time


  --no-bars, --no-commandbar, --no-topbar


                           don't show bottom, top bar or both


  --no-cursor              row cursor will be hidden


  --no-last-row-search     don't use the last pattern when starting a new search


  --no-scrollbar           don't show scrollbar


  --no-sound               don't use beep when scroll is not possible


  --tabular-cursor         cursor is visible only when data has table format


  --vertical-cursor        show vertical column cursor
Input format options:


  --csv                    input stream has csv format


  --csv-separator          char used as field separator


  --csv-header [on/off]    specify header line usage


  --skip-columns-like="SPACE SEPARATED STRING LIST"


                           columns with substr in name are ignored


  --csv-trim-width=NUM     trim value after NUM chars


  --csv-trim-rows=NUM      trim value after NUM rows


  --tsv                    input stream has tsv format
On exit options:


  --on-exit-reset          sends reset terminal sequence "\33c"


  --on-exit-clean          sends clean terminal sequence "\033[2J"


  --on-exit-erase-line     sends erase line terminal sequence "\33[2K\r"


  --on-exit-sgr0           sends sgr0 terminal sequence "\033[0;10m"
Watch mode options:


  -q, --query=QUERY        execute query


  -w, --watch time         the query (or read file) is repeated every time (sec)
Connection options:


  -d, --dbname=DBNAME      database name


  -h, --host=HOSTNAME      database server host (default: "local socket")


  -p, --port=PORT          database server port (default: "5432")


  -U, --username=USERNAME  database user name


  -W, --password           force password prompt
Debug options:


  --log=FILE               log debug info to file


  --wait=NUM               wait NUM seconds to allow attach from a debugger

pspg shares a lot of key commands with the less pager or the vi editor.

Options can also be passed within the PSPG environment variable. Configuration file is processed first. Options from the PSPG variable are processed afterwards step and command line options are processed at the end. One option can be processed multiple times, the last value wins.

Environment variables

Example of config file

The default path of config file is ~/.pspgconf.

The fields names can be different than from related command line options:

ascii_menu = false
bold_labels = false
bold_cursor = false
ignore_case = false
ignore_lower_case = false
no_cursor = false
no_sound = false
no_mouse = false
less_status_bar = false
no_highlight_search = false
no_highlight_lines = false
force_uniborder = false
show_rownum = false
without_commandbar = false
without_topbar = false
vertical_cursor = false
on_sigint_exit = false
no_sigint_search_reset = false
double_header = false
quit_on_f3 = false
pgcli_fix = false
xterm_mouse_mode = true
show_scrollbar = true
menu_always = false
empty_string_is_null = true
last_row_search = true
progressive_load_mode = true
highlight_odd_rec = false
hide_header_line = false
on_exit_reset = false
on_exit_clean = false
on_exit_erase_line = false
on_exit_sgr0 = false
direct_color = false
theme = 16
border_type = 2
default_clipboard_format = 0
clipboard_app = 0
hist_size = 500
esc_delay = -1

Themes

see http://okbob.blogspot.cz/2017/07/i-hope-so-every-who-uses-psql-uses-less.html

Keyboard commands

Backslash commands

The output can be redirected to any command when the name starts with pipe symbol:

\copy csv | less

Ending

The pager can be ended by pressing keys q or F10 or Esc 0. With option --on-sigint-exit then the pager is closed by pressing keys Ctrl+c or Esc Esc.

Use <kbd>Escape</kbd>, key instead <key>Alt</key> + <key>key</key>

pspg supports a possibility to use a sequence of keys Esc, key instead an combination of Alt+key. The interval between pressing Esc and key is limited by interval specified by option esc-delay or by configuration's option esc_delay. This is max delay time in ms. After this interval, the single pressing Esc is interpreted as Escape. -1 meas unlimited, 0 disables this feature.

Column search

Column search is case insensitive every time. Searched column is marked by vertical cursor. Last non empty string searching pattern is used when current searching pattern is empty string. Searching is starting after visible vertical column or on first visible not freezed columns (after some horizontal scrolling) or on first column. After last column searching starts from first again.

Export & Clipboard

For clipboard support the clipboard application should be installed: 1. wl-clipboard (Wayland),

1.

xclip (xwindows), 3. pbcopy (MacOS) or 4. clip.exe (WSL2).

pspg try to translate unicode symbol '∅' to NULL every time. If you don't use special setting by \pset null ..., then psql displays empty string instead NULL. pspg hasn't any special detection (in export routines) for this case. You should to check and enable or disable menu item Empty string is NULL.

pspg has automatic detection of clipboard application. Unfortunately, this detection should not to work for same cases. You can specify the application by specify number (1,2,3,4) to --clipboard-app option.

Formats make a difference! pspg copies records in CSV format by default, which uses comma separators and trims initial and trailing whitespace. Use "formatted text" to copy query output exactly, or choose one of the other available options.

Status line description

• V: [d/d d..d] - vertical cursor: (column number)/(columns) (char positions from) .. (char positions to)
• FC: d - freezed columns length in chars
• C: d..d/d - unfreezed visible data in chars (from .. to)/(total)
• L:[d + d d/d] - lines (number of first visible line) + (number of line of display), (current line)/(lines)
• d% - percent of already displayed data

Usage as csv viewer

It works well with miller http://johnkerl.org/miller/doc/index.html

mlr --icsv --opprint --barred put '' obce.csv | pspg --force-uniborder

New version has integrated csv support - just use --csv option.

It can be integrated into mc

• copy file from /etc/mc/mc.ext to your ~/.config/mc directory
• insert there

##csv
regex/\.csv


    View=pspg -f %f --csv

•

restart mc

Known issues

•

When you use pspg on Cygwin, then some temporary freezing of scrolling was reported In this case, please, use an option --no-sleep. I see slow scrolling (via scrollbar) inside konsole (KDE terminal). The option --no-sleep helps too.

Usage in watch mode

The result of query can be refreshed every n seconds. pspg remembers cursor row, possible vertical cursor, possible ordering. The refreshing should be paused by pressing space key. Repeated pressing of this key enables refreshing again.

pspg uses inotify API when it is available, and when input file is changed, then pspg reread file immediately. This behave can be disabled by option --no-watch-file or by specification watch time by option --watch.

Streaming modes

pspg can read a continuous stream of tabular data from pipe, named pipe or from file (with an option --stream or it can read a stream of queries from pipe or from file (with an option --querystream). In stream mode, only data in table format can be processed, because pspg uses empty line as separator between tables.

The query stream mode is an sequence of SQL statements separated by char GS (Group separator - 0x1D on separated line.

pavel@localhost ~]$ cat < /dev/pts/3 > ~/pipe
select 10
^]
select 20
^]
select *
from
pg_class
^]

Recommended psql configuration

you should to add to your profile:

#for Postgres 10 and older
export PAGER="pspg"
#for postgres 11 and newer
export PSQL_PAGER="pspg"
#or "\setenv PAGER pspg" to .psqlrc

and .psqlrc

\set QUIET 1
\pset linestyle unicode
\pset border 2
\pset null ∅
\unset QUIET

some possible configuration:

-- Switch pagers with :x and :xx commands
\set x '\\setenv PAGER less'
\set xx '\\setenv PAGER \'pspg -bX --no-mouse\''
:xx

LC_CTYPE should be correct. Mainly when you use unicode borders. ncurses doesn't display unicode borders (produced by psql) without correct setting of this variable. Is possible to check a value 'C.UTF8'.

Attention

When you use a option --only-for-tables, then

• set PAGER to pspg and PSQL_PAGER to less or
• set PAGER to less and PSQL_PAGER to pspg

MySQL usage

MariaDB [sakila]> pager pspg -s 14 -X --force-uniborder --quit-if-one-screen
PAGER set to 'pspg -s 14 -X --force-uniborder --quit-if-one-screen'
MariaDB [sakila]> select now();
MariaDB [sakila]> select * from nicer_but_slower_film_list limit 100;

SQLite

SQLite native client doesn't produce well formatted output, but can be forced to generate CSV format - and this format is well readable for pspg

sqlite3 -csv -header testdb.db 'select * from foo2' | pspg --csv --csv-header=on --double-header

pgcli

pgcli https://github.com/dbcli/pgcli/ needs the following configuration options (~/.config/pgcli/config):

pager = /usr/bin/pspg --csv --rr=2 --quit-if-one-screen --ignore-case --csv-header on --pgcli-fix
table_format = csv

Older version of pgcli had very slow output in tabular format. An workaround was using csv format. This should not be necessary on current versions when the performance issue was fixed. An option --pgcli-fix fixed import of partially broken csv format generated by pgcli. Modern version of pgcli doesn't need csv format, and doesn't need --pgcli-fix option.

pager = /usr/bin/pspg --rr=2 --quit-if-one-screen --ignore-case

sqlcl

As sqlcl doesn't currently support a pager option directly, you can either use a tool like qsh https://github.com/muhmud/qsh to work around this issue, or use the pspg.sql https://github.com/okbob/pspg/blob/master/scripts/sqlcl/pspg.sql script from this repo.

To use the script, start sqlcl as shown below (it's important to pass in the details of your current tty):

$ TTY=$(tty) sqlcl system/system @/path/to/pspg.sql

You can now have the results of a query sent to pspg like this:

SQL> pspg select * from user_tables;

nushell

The pspg supports default table_mode: rounded and table_mode: heavy.

The conversion to csv can be used too.

sys | get cpu | to csv | pspg --csv

Note: footer_mode should be disabled

nushell configuration:

$env.config.footer_mode = never
$env.config.table.header_on_separator = false
$env.config.ls.clickable_links = false
$env.config.table.mode = rounded

Note - mouse

pspg try to use xterm mouse mode 1002, when terminal and ncurses are not too antique. If there are problems with usage - unwanted visual artefacts when you move with mouse when some mouse button is pressed, then 1. please, report issue (please, attach log file), 2. use an option --no-xterm-mouse-mode and pspg will not try to activate this mode.

Note - true color themes on KDE konsole terminal

On my Fedora this terminal doesn't correctly display true color themes. The basic problem is in default TERM setting, that is xterm-256color. Unfortunately, the konsole terminal is not fully compatible with xterm, and doesn't allow color changing. You can force direct colors by using the option --direct-color or by setting TERM=xterm-direct. Second option is more correct setting of TERM variable to konsole-256color. In this case the pspg will map the true rgb colors to supported 256 colors.

Note - compilation issue

Some linker issues can be fixed by:

I changed
gcc -lncursesw pager.c -o pspg -ggdb
to
gcc pager.c -o pspg -ggdb -lncursesw

If you want to use pspg as Postgres client, then you need run configure --with-postgresql=yes. On Fedora with own Postgres build I had to install openssl-devel package and I had to set export PKG_CONFIG_PATH="/usr/local/pgsql/master/lib/pkgconfig/".

On FreeBsd you should to use gmake instead make.

Note - Installation details

When you compile code from source, run ./configure first. Sometimes ./autogen.sh first

If you would to display UTF-8 characters, then pspg should be linked with ncursesw library. UTF-8 characters are displayed badly when library ncursesw is used. You can see broken characters with incorrect locale setting too.

You can check wide chars support by pspg --version. Row ncurses with wide char support is expected. Re-run configure with --with-ncursesw option. When this command fails check if development package for ncursesw library is installed.

# brew install pspg

# apt-cache search pspg
# apt-get install pspg

# dnf install pspg

# apk add pspg

# emerge -av dev-db/pspg

# pkg install pspg

# pkg_add pspg

# port install pspg

Possible ToDo

• Store data in some column format (now data are stored like array of rows). With this change can be possible to operate over columns - hide columns, change width, cyclic iteration over columns, change order of columns, mark columns and export only selected columns (selected rows).
• Replace printing document directly to ncurses window by some smarter structure. Internally there are lot of checks and fixes to support complex dynamic layout. The possibly views should to remember first row, last row, current row. Now, these data are in global variables or in DataDesc and ScrDesc structures.

st_menu

This project uses st_menu library - implementation of CUA menubar and pulldown menu for ncurses https://github.com/okbob/ncurses-st-menu

Note

If you like it, send a postcard from your home country to my address, please:

Pavel Stehule
Skalice 12
256 01 Benesov u Prahy
Czech Republic

I invite any questions, comments, bug reports, patches on mail address pavel.stehule@gmail.com