Postgres 12 has introduced a new format in psql CLI. There are 9 types available. They are listed below.
nn
- n
- aligned
- asciidoc
- csv
- html
- latex
- latex-longtable
- troff-ms
- unaligned
- wrapped
n
n
n
n
n
n
n
n
n
nn
For more information look -> https://www.postgresql.org/docs/12/app-psql.html
nn
Let’s check each format. An example of aligned is shown here. This is not good with a wide table/output. It can overflow. Less readable. This is the default format.
nn
blipsnchitz=# \pset format alignednOutput format is aligned.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;n name | setting | unit | category | short_desc n-------------------------+------------+------+-------------------------------------+----------------------------------------------------------------------n allow_system_table_mods | off | | Developer Options | Allows modifications of the structure of system tables.n application_name | psql | | Reporting and Logging / What to Log | Sets the application name to be reported in statistics and logs.n archive_cleanup_command | | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point.n archive_command | (disabled) | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file.n archive_mode | off | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command.n(5 rows)nnblipsnchitz=# nnn
unaligned doesn’t use tabs. Hard to read.
nn
blipsnchitz=# \pset format unalignednOutput format is unaligned.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;nname|setting|unit|category|short_descnallow_system_table_mods|off||Developer Options|Allows modifications of the structure of system tables.napplication_name|psql||Reporting and Logging / What to Log|Sets the application name to be reported in statistics and logs.narchive_cleanup_command|||Write-Ahead Log / Archive Recovery|Sets the shell command that will be executed at every restart point.narchive_command|(disabled)||Write-Ahead Log / Archiving|Sets the shell command that will be called to archive a WAL file.narchive_mode|off||Write-Ahead Log / Archiving|Allows archiving of WAL files using archive_command.n(5 rows)nblipsnchitz=# nnn
wrapped is useful if your table fit into your terminal width. Otherwise, it does poorly.
nn
blipsnchitz=# \pset format wrappednOutput format is wrapped.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;n name | setting | unit | category | short_desc n----------------------+---------+------+--------------------------------+-----------------------------------------------n allow_system_table_m.| off | | Developer Options | Allows modifications of the structure of syst.n.ods | | | |.em tables.n application_name | psql | | Reporting and Logging / What t.| Sets the application name to be reported in s.n | | |.o Log |.tatistics and logs.n archive_cleanup_comm.| | | Write-Ahead Log / Archive Reco.| Sets the shell command that will be executed .n.and | | |.very |.at every restart point.n archive_command | (disabl.| | Write-Ahead Log / Archiving | Sets the shell command that will be called to.n |.ed) | | |. archive a WAL file.n archive_mode | off | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_c.n | | | |.ommand.n(5 rows)nnblipsnchitz=# nnn
This is the flashy new feature. I love it! It can be used to export too! Follows the RFC 4180.
nn
blipsnchitz=# \pset format csvnOutput format is csv.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;nname,setting,unit,category,short_descnallow_system_table_mods,off,,Developer Options,Allows modifications of the structure of system tables.napplication_name,psql,,Reporting and Logging / What to Log,Sets the application name to be reported in statistics and logs.narchive_cleanup_command,,,Write-Ahead Log / Archive Recovery,Sets the shell command that will be executed at every restart point.narchive_command,(disabled),,Write-Ahead Log / Archiving,Sets the shell command that will be called to archive a WAL file.narchive_mode,off,,Write-Ahead Log / Archiving,Allows archiving of WAL files using archive_command.nblipsnchitz=# nnn
psql supports few markup languages. An example of html is shown here.
nn
blipsnchitz=# \pset format htmlnOutput format is html.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;n<table border="1">n <tr>n <th align="center">name</th>n <th align="center">setting</th>n <th align="center">unit</th>n <th align="center">category</th>n <th align="center">short_desc</th>n </tr>n <tr valign="top">n <td align="left">allow_system_table_mods</td>n <td align="left">off</td>n <td align="left"> </td>n <td align="left">Developer Options</td>n <td align="left">Allows modifications of the structure of system tables.</td>n </tr>n <tr valign="top">n <td align="left">application_name</td>n <td align="left">psql</td>n <td align="left"> </td>n <td align="left">Reporting and Logging / What to Log</td>n <td align="left">Sets the application name to be reported in statistics and logs.</td>n </tr>n <tr valign="top">n <td align="left">archive_cleanup_command</td>n <td align="left"> </td>n <td align="left"> </td>n <td align="left">Write-Ahead Log / Archive Recovery</td>n <td align="left">Sets the shell command that will be executed at every restart point.</td>n </tr>n <tr valign="top">n <td align="left">archive_command</td>n <td align="left">(disabled)</td>n <td align="left"> </td>n <td align="left">Write-Ahead Log / Archiving</td>n <td align="left">Sets the shell command that will be called to archive a WAL file.</td>n </tr>n <tr valign="top">n <td align="left">archive_mode</td>n <td align="left">off</td>n <td align="left"> </td>n <td align="left">Write-Ahead Log / Archiving</td>n <td align="left">Allows archiving of WAL files using archive_command.</td>n </tr>n</table>n<p>(5 rows)<br />n</p>nblipsnchitz=# nnn
asciidoc is shown here.
nn
blipsnchitz=# \pset format asciidocnOutput format is asciidoc.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;nn[options="header",cols="<l,<l,<l,<l,<l",frame="none"]n|====n^l|name ^l|setting ^l|unit ^l|category ^l|short_descn|allow_system_table_mods |off | |Developer Options |Allows modifications of the structure of system tables.n|application_name |psql | |Reporting and Logging / What to Log |Sets the application name to be reported in statistics and logs.n|archive_cleanup_command | | |Write-Ahead Log / Archive Recovery |Sets the shell command that will be executed at every restart point.n|archive_command |(disabled) | |Write-Ahead Log / Archiving |Sets the shell command that will be called to archive a WAL file.n|archive_mode |off | |Write-Ahead Log / Archiving |Allows archiving of WAL files using archive_command.n|====nn....n(5 rows)n....nblipsnchitz=# nnn
latex looks like this.
nn
blipsnchitz=# \pset format latexnOutput format is latex.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;n\begin{tabular}{l | l | l | l | l}n\textit{name} & \textit{setting} & \textit{unit} & \textit{category} & \textit{short\_desc} \\n\hlinenallow\_system\_table\_mods & off & & Developer Options & Allows modifications of the structure of system tables. \\napplication\_name & psql & & Reporting and Logging / What to Log & Sets the application name to be reported in statistics and logs. \\narchive\_cleanup\_command & & & Write-Ahead Log / Archive Recovery & Sets the shell command that will be executed at every restart point. \\narchive\_command & (disabled) & & Write-Ahead Log / Archiving & Sets the shell command that will be called to archive a WAL file. \\narchive\_mode & off & & Write-Ahead Log / Archiving & Allows archiving of WAL files using archive\_command. \\n\end{tabular}nn\noindent (5 rows) \\nnblipsnchitz=# nnn
latex-longtable uses different packages.
nn
blipsnchitz=# \pset format latex-longtablenOutput format is latex-longtable.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;n\begin{longtable}{l | l | l | l | l}n\small\textbf{\textit{name}} & \small\textbf{\textit{setting}} & \small\textbf{\textit{unit}} & \small\textbf{\textit{category}} & \small\textbf{\textit{short\_desc}} \\n\midrulen\endfirstheadn\small\textbf{\textit{name}} & \small\textbf{\textit{setting}} & \small\textbf{\textit{unit}} & \small\textbf{\textit{category}} & \small\textbf{\textit{short\_desc}} \\n\midrulen\endheadn\raggedright{allow\_system\_table\_mods}n&n\raggedright{off}n&n\raggedright{}n&n\raggedright{Developer Options}n&n\raggedright{Allows modifications of the structure of system tables.} \tabularnewlinen\raggedright{application\_name}n&n\raggedright{psql}n&n\raggedright{}n&n\raggedright{Reporting and Logging / What to Log}n&n\raggedright{Sets the application name to be reported in statistics and logs.} \tabularnewlinen\raggedright{archive\_cleanup\_command}n&n\raggedright{}n&n\raggedright{}n&n\raggedright{Write-Ahead Log / Archive Recovery}n&n\raggedright{Sets the shell command that will be executed at every restart point.} \tabularnewlinen\raggedright{archive\_command}n&n\raggedright{(disabled)}n&n\raggedright{}n&n\raggedright{Write-Ahead Log / Archiving}n&n\raggedright{Sets the shell command that will be called to archive a WAL file.} \tabularnewlinen\raggedright{archive\_mode}n&n\raggedright{off}n&n\raggedright{}n&n\raggedright{Write-Ahead Log / Archiving}n&n\raggedright{Allows archiving of WAL files using archive\_command.} \tabularnewlinen\end{longtable}nblipsnchitz=# nnn
troff-ms is the format used in man pages.
nn
blipsnchitz=# \pset format troff-msnOutput format is troff-ms.nblipsnchitz=# nblipsnchitz=# SELECT name, setting, unit, category, short_desc FROM pg_settings LIMIT 5;n.LPn.TSncenter;nl | l | l | l | l.n\fIname\fP \fIsetting\fP \fIunit\fP \fIcategory\fP \fIshort_desc\fPn_nallow_system_table_mods off Developer Options Allows modifications of the structure of system tables.napplication_name psql Reporting and Logging / What to Log Sets the application name to be reported in statistics and logs.narchive_cleanup_command Write-Ahead Log / Archive Recovery Sets the shell command that will be executed at every restart point.narchive_command (disabled) Write-Ahead Log / Archiving Sets the shell command that will be called to archive a WAL file.narchive_mode off Write-Ahead Log / Archiving Allows archiving of WAL files using archive_command.n.TEn.DS Ln(5 rows)n.DEnblipsnchitz=# nnn
Tags
n
- n
- postgres
- postgresql
- psql
- pset
n
n
n
n
n