okbob/ pspg

(4.5.0) 18 days ago

Shell

C++

terminal-app

ncurses

PostgreSQL

psql

MySQL

mysql-client

View on GitHub

Need help with pspg?

Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

okbob

1.5K Stars

70 Forks

BSD 2-Clause "Simplified" License

828 Commits

3 Opened issues

Description

Unix pager (with very rich functionality) designed for work with tables. Designed for PostgreSQL, but MySQL is supported too. Works well with pgcli too. Can be used as CSV or TSV viewer too. It supports searching, selecting rows, columns, or block and export selected area to clipboard.

Services available

Need anything else?

Contributors list

Readme

pspg - Postgres Pager

Name: pspg
Brand: pspg
SKU: //okbob/pspg
Rating: 4.060222222222222 (1542 reviews)

Everybody who uses

psql

uses

less

pager. It is working well, but there is not any special support for tabular data. I found few projects, but no one was completed for this purpose. I decided to write some small specialized pager for usage as

psql

pager.

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

```
mysql
```
```
sqlite
```
```
pgcli
```
```
monetdb
```
```
Trino (formerly Presto SQL)
```

Main target

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

Video presentation

Screenshots

Options

```
-a
```
menu will use ascii borders
```
-b
```
black/white theme
```
-X
```
doesn't clean screen on the end
```
-s N
```
use theme (default theme is mc theme)
```
-c N
```
freeze first N columns
```
-f file
```
open file (default stdin)
```
--no-watch-file
```
don't watch changes of file
```
--force-uniborder
```
replace ascii border by unicode borders
```
-g --hilite-search
```
don't highlight lines for searches
```
-G --HILITE-SEARCH
```
don't highlight lines for searches ever
```
--help
```
show this help
```
-i --ignore-case
```
ignore case in searches that do not contain uppercase
```
-I --IGNORE-CASE
```
ignore case in all searches
```
--less-status-bar
```
status bar like less pager
```
--line-numbers
```
show line number column
```
--no-mouse
```
without own mouse handling (cannot be changed in app)
```
--no-sound
```
without sound effect
```
-F
```
,
```
--quit-if-one-screen
```
quit if content is one screen
```
-V
```
,
```
--version
```
show version
```
--about
```
show info about authors
```
--csv
```
input format is csv
```
--tsv
```
input format is tsv
```
--double-header
```
header line is doubled
```
--border
```
border used for formatted csv
```
--csv-separator
```
special char used as separator inside csv documents
```
--null striing
```
null string (default "")
```
--ni
```
not interactive mode (format csv to table and quit)
```
--no-cursor
```
the line cursor will be hidden
```
--no-commandbar
```
the bottom bar will be hidden
```
--no-topbar
```
the top bar will be hidden
```
--no-bars
```
both bars will be hidden
```
--no-scrollbar
```
scrollbar will be hidden
```
--no-sigint-search-reset
```
sigint is not used to reset searching
```
--no-xterm-mouse-mode
```
don't use extended mouse protocol
```
--tabular-cursor
```
cursor is displayed only for table
```
--only-for-tables
```
use std pager when content is not a table
```
--bold-labels
```
bold font for row, column labels
```
--bold-cursor
```
bold font for cursor
```
--vertical-cursor
```
show column cursor
```
--on-sigint-exit
```
double escape or ctrl c ending pager
```
--quit-on-f3
```
exit on press F3 (like mc viewer)
```
--skip-colums-like
```
space separated list of ignored columns (only for csv and tsv format)
```
-q
```
,
```
--query
```
execute query
```
-w
```
,
```
--watch n
```
repeat query execution every time sec
```
-d
```
,
```
--dbname
```
database name
```
-h
```
,
```
--host
```
database host name
```
-p
```
,
```
--port
```
database port
```
-U
```
,
```
--username
```
database user name
```
-W
```
,
```
--password
```
force password prompt
```
--pgcli-fix
```
used for fixing some pgcli related issues
```
--clipboard-app=[1,2,3]
```
specify clipboard application (1 wl-clipboard, 2 xclip, 3 pbcopy)
```
--no-sleep
```
disable waits used for reduction of terminal flickering

Options can be passed inside env variable

PSPG

too.

Themes

black & white
Midnight Commander like
FoxPro like
Pdmenu like
White theme
Mutt like
PCFand like
Green theme
Blue theme
Word Perfect like
Low contrast blue theme
Dark cyan/black mode
Paradox like
dBase IV retro style
dBase IV retro style (Magenta labels)
Red white theme
Simple theme
Solarized dark theme
Solarized light theme
Gruvbox light theme
Tao Light theme
FlatWhite theme
Relational pipes theme

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

Keyboard commands

0, 1, 2, 3, .., 9 - freeze first N columns
KEY_UP, k - navigate backward by one line
KEY_DOWN, j - navigate forward by one line
KEY_LEFT, h - scroll to left
KEY_RIGHT, l - scroll to right
Ctrl+Home, g - go to the start of file
Ctrl+End, G - go to the end of file
Alt+l - go to line number
H - go to first line of current window
M - go to half of current window
L - go to end of current window
PPAGE, Ctrl+b - backward one window
NPAGE, Ctrl+f, space - forward one window
HOME, ^ - go to begin of line, first column
END, $ - go to end of line, last column
Ctrl+e - scroll a window down
Ctrl+y - scroll a window up
Ctrl+d - forward a half window
Ctrl+u - backward a half window
s - save content to file
/ - search for a pattern which will take you to the next occurrence
? - search for a pattern which will take you to the previous occurrence
n - for next match
N - for next match in reverse direction
c - column search
Alt+c - switch (on, off) drawing line cursor
Alt+m - switch (on, off) own mouse handler
Alt+n - switch (on, off) drawing line numbers
Alt+v, double click on column header - switch (on, off) drawing column cursor
Mouse button wheel - scroll vertical
Alt+Mouse button wheel - scroll horizontal
F9 - show menu
q, F10, Esc 0 - quit
Alt+q - quit wit raw (unformatted) output
Alt+k, Alt+double click - switch bookmark
Alt+j - go to next bookmark
Alt+i - go to previous bookmark
Alt+o - flush bookmarks
a - sort ascendent
d - sort descendent
u - unsorted (sorted in origin order)
space - stop/continue in watch mode
R - Repaint screen and refresh input file
Ins - export row, column or cell to default target
shift+cursor... - define range
F3 - start/finish of selection rows
shift+F3 - start/finish of selection block
Ctrl + drag mouse - defines rows selection, on column header defines column selection

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.

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), 2. xlip (xwindows) or 3. pbcopy (MacOS).

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. Unfortunatelly, this detection should not to work for same cases. You can specify the application by specify number (1,2,3) to

--clipboard-app

option.

Status line description

```
V: [d/d d..d]
```
- vertical cursor: (column number)/(columns) (char possitions from) .. (char possitions 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

Know issues

When you use
```
pspg
```
on Cygwin, then some temporary freezing of scrolling was reported In this case, please, use an option
```
--no-sleep
```
.

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

Special options for reading from stream (file or FIFO)

pspg

can continually read from file or FIFO (named pipe). This behave should should be forced by option

--stream

When last writer to FIFO is ending, then

pspg

is ending too. This behave can be changed by option

--hold-stream

. Default value is 0. When

--hold-stream=1

then

pspg

will try to reopen FIFO and will hang until the FIFO will be opened for writing.

--hold-stream=2

is different strategy. The

pspg

reopen FIFO in write mode, and then FIFO will be opened until

pspg

is running.

Streaming modes

pspg

can read a stream of tabular data from 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

). It is good to use these option with option

--hold-stream

Without this option, there are possible unwanted issues when pipe will be closed.

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

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

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

On some old systems a compilation fails with error

/home/user/Src/pspg-0.6/src/pspg.c:2403: undefined reference to set_escdelay

In this case comment line with function set_escdelay

If you want to use

pspg

as Postgres client, then you need run

configure --with-postgres=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/"

Note - Installation

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 ncuresesw library is installed.

Homebrew (for Linux & MacOS)

# brew install pspg

Debian

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

Fedora (28 and later)

# dnf install pspg

RPM (CentOS/openSUSE/…)

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

Alpine Linux

# apk add pspg

Gentoo

# emerge -av dev-db/pspg

Arch Linux

The Arch User Repository contains two versions:

pspg is a fixed release.
pspg-git tracks the
```
master
```
branch.

Use the AUR helper of your choice or git and

makepkg

to install pspg.

FreeBSD

# pkg install pspg

Using MacPorts (MacOS only)

# port install pspg

Solaris

There are few issues requires manual code changes for successful compilation - we successfully tested

pspg

, but although

pspg

was linked with ncursesw libraries, the utf8 encoding support didn't work fully correctly - probably due some issues in

libc

library. There are problems with chars encoded to 3bytes - unicode borders, .. Two bytes unicode chars should be displayed well.

You can use

pspg

with usual accented chars, but unicode bordes should not be used. Replacement ascii borders by special borders chars (by ncurses technology) works well - looks on

Options|Force unicode borders

option.

Solaris
```
make
```
doesn't support conditional statements - should be removed So, remove unsupported functionality from
```
Makefile
```
(
```
ifdef
```
,
```
endif
```
), replace
```
-include
```
by
```
include
```
first.
After running
```
configure
```
remove link on
```
termcap
```
library from
```
config.make
```
. It is garabage produced by
```
readline
```
automake script. Combination with
```
ncurses
```
libraries makes some linking issues.

builtin libraries

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

OpenCSW development

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

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).

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 [email protected]