Repository files navigation

Everybody who usespsql also uses theless pager. Which, while it works well, has no specialsupport 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 apsql pager.

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

• mysql
• sqlite
• pgcli
• monetdb
• Trino (formerly Presto SQL)
• usql
• sqlcl (for oracle)
• nushell

• 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 - likemcview orFoxPro -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

Thepspg can be simply installed from Debian (Ubuntu) repositories. RedHat (Fedora) repositoriescontainspspg too:

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

Basic configuration is very simple - just set system environment variablePSQL_PAGER:

export PSQL_PAGER='pspg -X -b'

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

Installation on macOS/homebrew is simple bybrew install pspg.

[pavel@localhost ~]$ pspg --helppspg 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 exitOutput 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 NULLSearching 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 searchesInterface 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 cursorInput 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 formatOn 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 promptDebug 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 thePSPG environment variable. Configurationfile is processed first. Options from thePSPG variable are processed afterwardsstep and command line options are processed at the end. One option canbe processed multiple times, the last value wins.

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

ascii_menu = falsebold_labels = falsebold_cursor = falseignore_case = falseignore_lower_case = falseno_cursor = falseno_sound = falseno_mouse = falseless_status_bar = falseno_highlight_search = falseno_highlight_lines = falseforce_uniborder = falseshow_rownum = falsewithout_commandbar = falsewithout_topbar = falsevertical_cursor = falseon_sigint_exit = falseno_sigint_search_reset = falsedouble_header = falsequit_on_f3 = falsepgcli_fix = falsexterm_mouse_mode = trueshow_scrollbar = truemenu_always = falseempty_string_is_null = truelast_row_search = trueprogressive_load_mode = truehighlight_odd_rec = falsehide_header_line = falseon_exit_reset = falseon_exit_clean = falseon_exit_erase_line = falseon_exit_sgr0 = falsedirect_color = falsetheme = 16border_type = 2default_clipboard_format = 0clipboard_app = 0hist_size = 500esc_delay = -1

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

The theme can be customized over base and menu templates referencing thebuilt-in themes. The custom theme file should be saved in directory withpspgconfiguration. The name of this file should be .pspg_theme_xxx. The customtheme can be selected by command line option--custom-style=name or bycommand\ctheme name.

Example of a custom theme file (named.pspg_theme_mc2 (it can be activated bycommand\ctheme mc2)):

template = 1template_menu = 3background = black, whitedata = black, whitelabel = black, white, italic, boldborder = #000000, whitefooter = lightgray, whitecursor_data = blue, white, italic, bold, dim, reversecursor_border = blue, blue , italic, bold, dim, reversecursor_label = blue, white, italic, bold, dim, reversecursor_footer = blue, white, italic, bold, dim, reversecursor_bookmark = red, white, italic, bold, dim, reversecross_cursor = white, blue, italic, boldcross_cursor_border = brightblue, bluestatus_bar = black, lightgraytitle = black, lightgrayscrollbar_arrows = black, whitescrollbar_background = lightgray, whitescrollbar_slider = white, gray

Some keys can be marked by symbol*. Marked keys are used for odd records.

data* = black, lightgraylabel* = black, lightgray, italic, boldborder* = #000000, lightgray

template andtemplate_menu set fallback values for any keys not specifiedin the custom theme.template_menu in particular is currently the only way tocustomize the F9 menu appearance.

ANSI colorsBlack,Red,Green,Brown,Blue,Magenta,Cyan,LightGray,Gray,BrightRed,BrightGreen,Yellow,BrightBlue,BrightMagenta,BrightCyan,White, andDefault will display as yourterminal emulator configures them. Alternatively, you can specify hex RGBvalues#FF00FF.

Styles are any combination of:bold,italic,underline,reverse,standout,dim.

If the format of some key is not correct, then this row is ignored. For debugging ofcustom theme is good to startpspg with option--log. An information about brokendefinitions are stored in log file.

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

\copy csv | less

The pager can be ended by pressing keysq orF10 orEsc0.With option--on-sigint-exit then the pager is closed by pressing keysCtrl+corEscEsc.

pspg supports a possibility to use a sequence of keysEsc,key instead ancombination ofAlt+key. The interval between pressingEsc andkey is limited by interval specified by optionesc-delay or by configuration'soptionesc_delay. This is max delay time in ms. After this interval, the single pressingEscis interpreted asEscape. -1 meas unlimited, 0 disables this feature.

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 (aftersome horizontal scrolling) or on first column. After last column searching starts from first again.

For clipboard support the clipboard application should be installed: 1. wl-clipboard (Wayland),2. 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 settingby\pset null ..., thenpsql displays empty string instead NULL.pspg hasn't any specialdetection (in export routines) for this case. You should to check and enable or disable menuitemEmpty string is NULL.

pspg has automatic detection of clipboard application. Unfortunately, this detection shouldnot 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 commaseparators andtrims initial and trailing whitespace. Use "formatted text" to copy query outputexactly, or choose one of the other available options.

• 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

It works well with millerhttp://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 intomc

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

##csvregex/\.csv    View=pspg -f %f --csv

• restartmc

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

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 pressingspace key. Repeated pressing of this key enables refreshing again.

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

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 beprocessed, becausepspg uses empty line as separator between tables.

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

pavel@localhost ~]$ cat < /dev/pts/3 > ~/pipeselect 10^] select 20^]select *frompg_class^]

you should to add to your profile:

#for Postgres 10 and olderexport PAGER="pspg"#for postgres 11 and newerexport 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 bypsql) withoutcorrect setting of this variable. Is possible to check a value 'C.UTF8'.

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

• setPAGER topspg andPSQL_PAGER toless or
• setPAGER toless andPSQL_PAGER topspg

MariaDB [sakila]> pager pspg -s 14 -X --force-uniborder --quit-if-one-screenPAGER 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 native client doesn't produce well formatted output, but can be forcedto generate CSV format - and this format is well readable forpspg

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

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-fixtable_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 bypgcli. Modern version ofpgcli doesn't need csv format,and doesn't need--pgcli-fix option.

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

Assqlcl doesn't currently support a pager option directly, you can either use a tool likeqsh to work around this issue, or use thepspg.sql script from this repo.

To use the script, startsqlcl 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 topspg like this:

SQL> pspg select * from user_tables;

Thepspg supports defaulttable_mode:rounded andtable_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

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

On my Fedora this terminal doesn't correctly display true color themes. The basic problemis in defaultTERM setting, that isxterm-256color. Unfortunately, thekonsole terminalis not fully compatible withxterm, and doesn't allow color changing. You can force directcolors by using the option--direct-color or by settingTERM=xterm-direct. Second optionis more correct setting ofTERM variable tokonsole-256color. In this case thepspg willmap the true rgb colors to supported 256 colors.

Some linker issues can be fixed by:

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

If you want to usepspg as Postgres client, then you need runconfigure --with-postgresql=yes. On Fedora with own Postgres buildI had to installopenssl-devel package and I had to setexport PKG_CONFIG_PATH="/usr/local/pgsql/master/lib/pkgconfig/".

On FreeBsd you should to usegmake insteadmake.

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

If you would to display UTF-8 characters, thenpspg should be linked withncurseswlibrary. UTF-8 characters are displayed badly when libraryncursesw is used. You cansee broken characters with incorrect locale setting too.

You can check wide chars support bypspg --version. Rowncurses with wide char supportis expected. Re-runconfigure with--with-ncursesw option. When this command fails checkif development package for ncursesw library is installed.

# brew install pspg

You can compile easilypspg withoutbrew, but you needgnu readline library. MacOS usesby default readline emulated over libedit, butpspg requires full gnu readline library.

LDFLAGS="-L/usr/local/opt/readline/lib" CPPFLAGS="-I/usr/local/opt/readline/include" ./configureLDFLAGS="-L/usr/local/opt/readline/lib" CPPFLAGS="-I/usr/local/opt/readline/include" make

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

# dnf install pspg

The pspg is available from community repositoryhttps://yum.postgresql.org/packages.php

# apk add pspg

# emerge -av dev-db/pspg

The Arch User Repository contains two versions:

• pspg is a fixed release.
• pspg-git tracks themaster branch.

Use the AUR helper of your choice or git andmakepkg to install pspg.

# pkg install pspg

# pkg_add pspg

More about it

# port install pspg

pspg can be simply used on MS Windows by using wsl2. I tested it, and it is working without problems.

• In terminal executewsl --install -d Ubuntu-22.04

• In terminal open Ubuntu session

sudo apt-get updatesudo apt-get install pspgsudo apt-get install postgresql postgresql-contrib# set password for user postgressudo passwd postgressu - postgrespsql postgres>> create role pavel login;\qexittouch ~/.psqlrcmcedit .psqlrc\pset linestyle unicode\pset border 2\setenv PSQL_PAGER 'pspg -b -X'# press F2 and F10psql postgres

there is not any difference from installation and work on Ubuntu (Debian)

pspg is not ported to MS Windows yet. There is the dependency on ncurses and correctly (fully)implemented functionnewterm (pdcurses does this only on Unix platforms). It can workwith WSL2 maybe (I didn't test it). An alternative can be usingless pager, that is portedto some MS Win enviroments.less depends ontermcap, and it is little bit more portablethanpspg (termcal is low layer of ncurses).less supports fixed rows and with--chop-long-linesoption or just-S can be used as pager forpspg.

export PSQL_PAGER="less --chop-long-lines --header 1"

There are few issues requires manual code changes for successful compilation - we successfullytestedpspg, but althoughpspg was linked with ncursesw libraries, the utf8 encoding supportdidn't work fully correctly - probably due some issues inlibc library. There are problems withchars encoded to 3bytes - unicode borders, .. Two bytes unicode chars should be displayed well.

You can usepspg with usual accented chars, but unicode borders should not be used. Replacementascii borders by special borders chars (by ncurses technology) works well - looks onOptions|Force unicode bordersoption.

• Solarismake doesn't support conditional statements - should be removed So, remove unsupportedfunctionality fromMakefile (ifdef,endif), replace-include byinclude first.

• After runningconfigure remove link ontermcap library fromconfig.make. It is garbageproduced byreadline automake script. Combination withncurses libraries makes somelinking issues.

export CURSES_CFLAGS="-I/usr/include/ncurses/"export PANEL_LIBS="-lpanelw"./configure

export CFLAGS="-m64 -I/opt/csw/include"export LDFLAGS="-L/opt/csw/lib/64 -R/opt/csw/lib/64"export PKG_CONFIG_PATH="/opt/csw/lib/64/pkgconfig"./configure

• Store data in some column format (now data are stored like array of rows). With this change canbe 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. Internallythere are lot of checks and fixes to support complex dynamic layout. The possibly views shouldto remember first row, last row, current row. Now, these data are in global variables or inDataDesc and ScrDesc structures.

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

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

Pavel StehuleSkalice 12256 01 Benesov u PrahyCzech Republic

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