Product Documentation

Appearance

ybsql Display Properties ​

The following settings modify the display of ybsql output. You can set these display options in two different ways:

  • When starting a session:
ybsql --pset=NAME[=VALUE]
  • Inside a session:
\pset NAME [VALUE] inside ybsql
border

In HTML format, a number that defines the border= attribute. In most other formats, only values 0 (no border), 1 (internal dividing lines), and 2 (table frame) make sense; values above 2 will be treated the same as border=2. In the latex and latex-longtable formats, a value of 3 adds dividing lines between data rows.

columns

Target width for wrapped format, as well as the width limit for determining whether output is wide enough to require the pager or to switch to the vertical display in expanded auto mode. If set to zero (the default), the environment variable COLUMNS controls the target width (or the detected screen width controls it if COLUMNS is not set). If COLUMNS is zero, the wrapped format only affects screen output. If COLUMNS is non-zero, file and pipe output is wrapped to that width as well.

expanded (or x)

Set to on or off, which enables or disables expanded mode, or auto. If no value is set, the \pset expanded toggles between on and off. In expanded mode, query results are displayed in two columns, with the column name on the left and the data on the right. This mode is useful if the data would not fit on the screen in the normal "horizontal" mode. When auto is set, the expanded mode is used whenever the query output is wider than the screen; otherwise, the regular mode is used. The auto setting is only effective for aligned and wrapped formats. For other formats, auto behaves as if the expanded mode is off.

fieldsep

Field separator to be used in unaligned output format so you can create tab- or comma-separated output, for example, which other programs might prefer. To set a tab as the field separator, type \pset fieldsep '\t'. The default field separator is '|' (a vertical bar).

fieldsep_zero

Zero-byte field separator for use in unaligned output format.

footer

Set to on or off, which enables or disables display of the table footer (row count). If no value is specified, \pset footer toggles between on and off.

format

Output format:

  • unaligned: all columns of a row on one line, separated by the current field separator. Useful for output intended to be read in by other programs (for example, tab-separated or comma-separated format).
  • aligned: standard, human-readable, well-formatted text output (the default).
  • wrapped: similar to aligned but wraps wide data values across lines so the output fits in the target column width. The target width is determined as described under the columns option. ybsql does not attempt to wrap column header titles; therefore, wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.
  • html, asciidoc, latex, latex-longtable, troff-ms: table output intended to be included in documents that use the respective mark-up language.
linestyle

Border line drawing style: ascii (the default) or unicode. This option only applies to the aligned and wrapped formats.

  • ascii: plain ASCII characters. Newlines in data are shown by a + symbol in the right-hand margin. When the wrapped format wraps data from one line to the next without a newline character, a dot (.) is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.
  • unicode: Unicode box-drawing characters. Newlines in data are shown using a carriage return symbol in the right-hand margin. When the data is wrapped from one line to the next without a newline, an ellipsis is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.

When the border setting is greater than zero, the linestyle option also determines the characters with which the border lines are drawn.

null

String to print when a null value is displayed. The default is to print nothing, which is easily mistaken for an empty string. For example: \pset null '(null)'

pager

Control use of a pager program for query and ybsql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.

  • off: do not use the pager program.
  • on: use the pager when the output is to a terminal and does not fit on the screen.
  • always: use the pager for all terminal output regardless of whether it fits on the screen.
  • \pset pager without a value toggles pager use on and off.
pager_min_lines

If set a number greater than the page height, the pager program is not called unless there are at least this many lines of output to show. The default setting is 0.

recordsep

Record (line) separator to use for unaligned format. The default is a newline character.

recordsep_zero

Zero-byte record separator for unaligned format.

tableattr (or T)

In HTML format, set attributes to be placed inside the table tag, such as cellpadding or bgcolor. (Note that border can be set with \pset border). If no value is given, the table attributes are unset.

In latex-longtable format, control the proportional width of each column that contains a left-aligned data type. Specified as a whitespace-separated list of values; for example: '0.2 0.2 0.6'. Unspecified output columns use the last specified value.

title

Descriptive table title for subsequent table output. If no value is specified, the title is unset.

tuples_only

on or off, which enables or disables tuples-only mode. If no value is specified, the command toggles between regular and tuples-only output. Regular output includes column headers, titles, and footers. Tuples-only mode shows only the table data (query results).

unicode_border_linestyle, unicode_column_linestyle, unicode_header_linestyle

single or double for Unicode border lines, column lines, and headers.

Examples ​

For readability, change the default aligned format for query output to unaligned so that all of the columns in a row appear on one line. You can use this command in combination with \pset recordsep '\n\n' to add an extra newline between each row. For example:

Output format is unaligned.
premdb=# \pset recordsep '\n\n'
Record separator is "

".
premdb=# SELECT query_id, query_text FROM sys.log_query limit 3;
query_id|query_text

19340|ANALYZE HLL public.awayteam

19328|insert into awayteam select atid,name from team;

19359|ANALYZE HLL public.hometeam

(3 rows)

Set an output string for NULL values. For example:

premdb=# \pset null NULL
Null display is "NULL".
premdb=# 
premdb=# select * from season where winners is null;
 seasonid | season_name | numteams | winners 
----------+-------------+----------+---------
      25 | 2016-2017   |       20 | NULL
(1 row)

Parent topic:ybsql Properties and Variables