
<p>While it’s tempting to use the latest tools, there’s often a powerful enough solution that already exists. For connecting to and querying a PostgreSQL database, that tool is psql.</p>



<h2 class="wp-block-heading">Commands</h2>



<p>Most psql configuration can be done once the program is started using slash commands. For the entire list of commands, use \?.</p>



<p>It’s important to remember that configuration only persists for the session. To keep your config across all your sessions, create a .psqlrc file in your home directory.</p>



<h2 class="wp-block-heading">Timing</h2>



<p>Our favorite config option is \timing on. \timing on tells psql to print the runtime of the query after execution. It’s great for figuring out why your queries are slow.</p>



<h2 class="wp-block-heading">Null Printing</h2>



<p>Another useful setting is \pset null. By default, NULL values show up as empty strings. This makes it impossible to tell if a nullable varchar is ’‘ or NULL. With \pset null ‘[NULL]’, psql will print the word [NULL] instead.</p>



<p>For example:</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/optimizing1.png" alt="psql null" class="wp-image-85453"/></figure>



<h2 class="wp-block-heading">Extended Output</h2>



<p>Want a better way of looking at each record individually? \x on is your command. It sets extended output. This prints each record as a series of rows, rather than in a table.</p>



<p>\x auto will automatically choose extended output after a certain number of rows and columns.</p>



<p>For example:</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/optimizing2.png" alt="psql slashx" class="wp-image-85447"/></figure>



<h2 class="wp-block-heading">Config Variables</h2>



<p>Like many shells, psql uses config vars to control some behaviors. Use \set to set your variable values.</p>



<h2 class="wp-block-heading">History control</h2>



<p>We often run the same or similar queries on our database. Having psql keep track of our query history is a great way to save keystrokes. You can even use separate history files per database.</p>



<p>Sometimes, you have a query that shouldn’t be saved (such as a huge insert or select). By setting HISTCONTROL to ignorespace, a leading space before your query [“ select foo”] prevents that query from being saved.</p>



<p>Once you have a long and rich history, using ctrl-r to search for previous queries is extremely handy.</p>



<pre class="wp-block-code"><code>\set HISTCONTROL ignoreboth
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 20000 -- Huge history file -- ctrl-r is useful</code></pre>



<p>Here we entered ctrl-r, then typed in select * from to find a previous query:</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/optimizing3.png" alt="r-i search" class="wp-image-85465"/></figure>



<h2 class="wp-block-heading">Quit Protection</h2>



<p>Ever type ctrl-d only to accidentally close the wrong session and lose all your temp tables? Then you’ll appreciate \set IGNOREEOF 5. This stops ctrl-d from quitting. Instead, type \q to quit.</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/optimizing4.png" alt="psql ctrl d" class="wp-image-85459"/></figure>



<h2 class="wp-block-heading">Logging</h2>



<p>Adding -L will log your entire session to a file. This is great for when you want to remember how long a query took the other day, or what the results were from a query that has scrolled out of your terminal.</p>



<p>The easiest way to specify this is a shell alias.</p>



<pre class="wp-block-code"><code>alias psql="psql -L ~/psql.log"</code></pre>



<p>Put this in your .bashrc to have it run when you start your shell.</p>



<h4 class="wp-block-heading">All Together:</h4>



<pre class="wp-block-code"><code>-- Put this alias in .bashrc to turn on session logging
-- alias psql='psql -eL /tmp/psql.log'
\timing on
\setenv LESS -imx4F
\x auto

-- Put a space in front of queries you don't want to save
\set HISTCONTROL ignorespace
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 20000
 -- Huge history file -- ctrl-r is useful
\pset null '[NULL]'
-- Don't exit with ctrl-d unless you press it 5 times
\set IGNOREEOF 5</code></pre>


Optimizing Your psql

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article