In PostgreSQL, we can control how the query result should be displayed to the user in the psql terminal. We can do this using the \pset command in PostgreSQL. The \pset command means print settings. In PostgreSQL, we can use nearly 24 options with the \pset command, and we can see the available options with \pset like this.
Just enter the \pset command in the psql terminal and then press the tab key.
postgres=# \pset
Result :
border display_true footer numericlocale recordsep_zero unicode_border_linestyle
columns expanded format pager tableattr unicode_column_linestyle
csv_fieldsep fieldsep linestyle pager_min_lines title unicode_header_linestyle
display_false fieldsep_zero null recordsep tuples_only xheader_width
You can also see the default values of the print settings like this.
postgres=# \pset
Result :
border 1
columns 40
csv_fieldsep ','
display_false 'f'
display_true 't'
expanded off
fieldsep '|'
fieldsep_zero off
footer on
format wrapped
linestyle ascii
null ''
numericlocale off
pager 1
pager_min_lines 0
recordsep '\n'
recordsep_zero off
tableattr
title
tuples_only on
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width full
So, here we cover only the important attributes we can use with the \pset command.
Now, create a sample table and insert some random values like this.
CREATE TABLE employees (
id INT,
name TEXT,
department TEXT,
salary NUMERIC(10,2),
active BOOLEAN,
joining_date DATE
);
INSERT INTO employees VALUES
(1,'Alice','HR',45000.50,true,'2022-01-15'),
(2,'Bob','Sales',60000,false,'2021-06-20'),
(3,'Charlie','IT',75000.75,true,'2023-03-12');
Now, select the data from the table named employee and look at its result.
select * from employees ;
Result :
id | name | department | salary | active | joining_date
----+---------+------------+----------+--------+--------------
1 | Alice | HR | 45000.50 | t | 2022-01-15
2 | Bob | Sales | 60000.00 | f | 2021-06-20
3 | Charlie | IT | 75000.75 | t | 2023-03-12
(3 rows)
This is the normal result we can get from the psql terminal.
Now, let’s try the print settings options one by one.
\pset border
\pset border 0
Now, check the result from the employee table.
SELECT * FROM employees;
Result :
id name department salary active joining_date
-- ------- ---------- -------- ------ ------------
1 Alice HR 45000.50 t 2022-01-15
2 Bob Sales 60000.00 f 2021-06-20
3 Charlie IT 75000.75 t 2023-03-12
(3 rows)
Here, we can see that the border around the query results is reduced.
Now try the border attribute with some large values.
\pset border 1
Check the query result again.
SELECT * FROM employees;
Result :
id | name | department | salary | active | joining_date
----+---------+------------+----------+--------+--------------
1 | Alice | HR | 45000.50 | t | 2022-01-15
2 | Bob | Sales | 60000.00 | f | 2021-06-20
3 | Charlie | IT | 75000.75 | t | 2023-03-12
(3 rows)
Now try another value like 2.
\pset border 2
Check the result again.
SELECT * FROM employees;
Result :
+----+---------+------------+----------+--------+--------------+
| id | name | department | salary | active | joining_date |
+----+---------+------------+----------+--------+--------------+
| 1 | Alice | HR | 45000.50 | t | 2022-01-15 |
| 2 | Bob | Sales | 60000.00 | f | 2021-06-20 |
| 3 | Charlie | IT | 75000.75 | t | 2023-03-12 |
+----+---------+------------+----------+--------+--------------+
(3 rows)
Now, create another table for using the column attribute with the \pset command.
CREATE TABLE products (
id INT,
description TEXT
);
INSERT INTO products VALUES
(
1,
'This is a very long product description that demonstrates how wrapped output works inside psql when the terminal width is small.'
);
Check the results from the products table.
select * from products ;
Result :
id | description
----+----------------------------------------------------------------------------------------------------------------------------------
1 | This is a very long product description that demonstrates how wrapped output works inside psql when the terminal width is small.
(1 row)
This is the normal query result view in postgres. Now, use the format wrapped attribute like this.
\pset format wrapped
\pset format wrapped
Check the results from the products table like this.
select * from products ;
Result :
id | description
----+----------------------------------------------------------------------------------------------------------------------------------
1 | This is a very long product description that demonstrates how wrapped output works inside psql when the terminal width is small.
(1 row)
This is mainly used to set the wider query results into the target result box.
\pset columns
Now use the columns attribute like this.
\pset columns 40
Check the results again.
select * from products ;
Result :
id | description
----+-----------------------------------
1 | This is a very long product descr.
|.iption that demonstrates how wrap.
|.ped output works inside psql when.
|. the terminal width is small.
(1 row)
This tells psql to format the output to fit within approximately 40 characters whenever the selected output format supports wrapping.
\x - Expanded display
\x
Expanded display is on.
SELECT * FROM employees;
Result :
+-[ RECORD 1 ]-+------------+
| id | 1 |
| name | Alice |
| department | HR |
| salary | 45000.50 |
| active | t |
| joining_date | 2022-01-15 |
+-[ RECORD 2 ]-+------------+
| id | 2 |
| name | Bob |
| department | Sales |
| salary | 60000.00 |
| active | f |
| joining_date | 2021-06-20 |
+-[ RECORD 3 ]-+------------+
| id | 3 |
| name | Charlie |
| department | IT |
| salary | 75000.75 |
| active | t |
| joining_date | 2023-03-12 |
+--------------+------------+
(3 row)
This is mainly used to change the results display format from horizontal view to vertical view.
\pset footer off
SELECT * FROM employees;
Result :
+----+---------+------------+----------+--------+--------------+
| id | name | department | salary | active | joining_date |
+----+---------+------------+----------+--------+--------------+
| 1 | Alice | HR | 45000.50 | t | 2022-01-15 |
| 2 | Bob | Sales | 60000.00 | f | 2021-06-20 |
| 3 | Charlie | IT | 75000.75 | t | 2023-03-12 |
+----+---------+------------+----------+--------+--------------+
This is mainly used to hide the footer, like the row counts.
\pset format unaligned
\pset format unaligned
Check the results from the employees table.
SELECT * FROM employees;
Result :
id|name|department|salary|active|joining_date1|Alice|HR|45000.50|t|2022-01-152|Bob|Sales|60000.00|f|2021-06-203|Charlie|IT|75000.75|t|2023-03-12
Here, the results are in an aligned format, and there is no proper border here.
\pset format csv
\pset format csv
Check the results from the employees table.
SELECT * FROM employees;
Result :
id,name,department,salary,active,joining_date
1,Alice,HR,45000.50,t,2022-01-15
2,Bob,Sales,60000.00,f,2021-06-20
3,Charlie,IT,75000.75,t,2023-03-12
Now the results are in the csv format.
\pset format html
\pset format html
Check the results from the employees table.
SELECT * FROM employees;
Result :
<table border="2">
<tr>
<th align="center">id</th>
<th align="center">name</th>
<th align="center">department</th>
<th align="center">salary</th>
<th align="center">active</th>
<th align="center">joining_date</th>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">Alice</td>
<td align="left">HR</td>
<td align="right">45000.50</td>
<td align="left">t</td>
<td align="left">2022-01-15</td>
</tr>
<tr valign="top">
<td align="right">2</td>
<td align="left">Bob</td>
<td align="left">Sales</td>
<td align="right">60000.00</td>
<td align="left">f</td>
<td align="left">2021-06-20</td>
</tr>
<tr valign="top">
<td align="right">3</td>
<td align="left">Charlie</td>
<td align="left">IT</td>
<td align="right">75000.75</td>
<td align="left">t</td>
<td align="left">2023-03-12</td>
</tr>
</table>
Now we can see the results in the html format like this.
\pset format asciidoc
\pset format asciidoc
Check the results from the employees table.
SELECT * FROM employees;
Result :
[options="header",cols=">l,<l,<l,>l,<l,<l",frame="all",grid="all"]
|====
^l|id ^l|name ^l|department ^l|salary ^l|active ^l|joining_date
|1 |Alice |HR |45000.50 |t |2022-01-15
|2 |Bob |Sales |60000.00 |f |2021-06-20
|3 |Charlie |IT |75000.75 |t |2023-03-12
|====
The \pset format asciidoc option formats query results as an AsciiDoc table instead of the default aligned table.
\pset format latex
\pset format latex
Check the results from the employees table.
SELECT * FROM employees;
Result :
\begin{tabular}{| r | l | l | r | l | l |}
\hline
\textit{id} & \textit{name} & \textit{department} & \textit{salary} & \textit{active} & \textit{joining\_date} \\
\hline
1 & Alice & HR & 45000.50 & t & 2022-01-15 \\
2 & Bob & Sales & 60000.00 & f & 2021-06-20 \\
3 & Charlie & IT & 75000.75 & t & 2023-03-12 \\
\hline
\end{tabular}
\noindentThe \pset format latex option formats query results as a LaTeX tabular environment instead of the default aligned table.
\pset tuples_only
\pset tuples_only
Check the results from the employees table.
select * from employees ;
Result :
1 | Alice | HR | 45000.50 | t | 2022-01-15
2 | Bob | Sales | 60000.00 | f | 2021-06-20
3 | Charlie | IT | 75000.75 | t | 2023-03-12
Here, we can only see the results without the table header.
The \pset command is one of the most useful features of the PostgreSQL psql client for controlling how query results are displayed. Whether you want cleaner tables, wrapped output for long text, CSV output for exporting data, or HTML, AsciiDoc, and LaTeX formats for documentation, \pset provides a simple way to customize the presentation without changing the query or the underlying data.
Knowing how to use these print options will be useful in optimizing work with PostgreSQL, because these options become extremely helpful in case you need to debug queries, create reports, export results, or write technical documentation. Using the right \pset option will help enhance the output of the query.