Customizing my postgresql shell
Reviews
Some nice postgresql configuration to set when using
psql.
As a developer your CLI is your home. You spend a lifetime of person-years in your shell and even small optimizations can pay major dividends to your efficiency. For anyone that works with Postgres and likely the psql editor, you should consider investing some love and care into psql. A little known fact is that psql has a number of options you can configure it with, and these configuration options can all live within an rc file called psqlrc in your home directory. Here is my .psqlrc file, which I’ve customized to my liking. Let’s walk through some of the commands within my .psqlrc file:
\set QUIET 1
\pset null '¤'
\set PROMPT1 '%[%033[1m%][%/] # '
-- SELECT * FROM<enter>. %R shows what type of input it expects.
\set PROMPT2 '... > '
\timing
\x auto
\set VERBOSITY verbose
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\unset QUIET
First you see that we set QUIET 1, this makes it less noisy when we start up, we also unset QUIET at the end so it’s back to a standard psql shell later.
See all the nulls
Nulls are useful to be aware of within your database. For example null is not = to ‘’ and that might have some impact on your app. If you’re not experienced working with nulls, then here’s a great post on some of the quirks about Null. Regardless of how familiar you are in dealing with nulls, having them clearly displayed in your output can be helpful. To display nulls clearly, we use pset. With pset you can customize many things such as borders, you can tweak the footer, and more—and easily the most handy thing you can do is specify a character for null. You can put any character you like in here, so have fun and make your nulls super noticeable.
Know whether you’re connected to prod, staging, or dev
It’s incredibly common to connect to a number of different databases that have the same schema. At a minimum you have prod, staging, dev. No matter how much care you take in running a command against an environment, it’s always nice to have a signpost that tells you what you’re connected to. A nice extra level of awareness is to clearly display the database name that you’re connected to. In my example, I simply call out the database name I’m connected to and give some nice formatting to it, but there’s so much more you can do. If you really want to have fun, take a look at the PostgreSQL manual to learn what can be printed about your database connection.
Time all the things
It’s not that I want to be obsessive about every Postgres query that I run, but knowing how fast a query has run is almost never a burden. For this reason I turn on timing by default. With timing on, I can see the amount of time queries took to run within the footer of every query.
Log all the things
From time to time I write some pretty long and crazy SQL. Many of these queries get saved in more canned reports or within a repo. But… I’m not perfect :-) and sometimes I spend a while on a query, get the answer to my question, and then forget about it—only to come back months later and try to recreate the query as close as possible. Sure it’s faster to create a complex SQL query the second time, but not having to write it at all is even better. To preempt this frustration from happening, I keep a log of all of my queries and save them on a per-database level. Now when I forget a complex and crazy query and want to recall it two months later, it’s as simple as browsing through my history file.
Let Postgres format everything for you
Apps have a huge variance in the number of columns within the table. Sometimes you have really small ones, sometimes much wider. Within Postgres you can toggle your output to be expanded or more compressed with \x. With expanded output turned on, I get a query result formatted like:
SELECT *
FROM users;
-[ RECORD 1 ]---+-------------------------------------
id | 0a7a3cde-3613-4073-86a7-6a19b4e62bbe
email | craig@citusdata.com
name | Craig
last_sign_in_at | 2017-05-25 14:23:29.041527+00
created_at | 2016-02-18 03:03:26.403108+00
With it turned off, I get all my record results on a single row:
SELECT *
FROM users;
id | email | name | last_sign_in_at | created_at
--------------------------------------+---------------------+-------+-------------------------------+-------------------------------
0a7a3cde-3613-4073-86a7-6a19b4e62bbe | craig@citusdata.com | Craig | 2017-05-25 14:23:29.041527+00 | 2016-02-18 03:03:26.403108+00
The first is great for wider tables, the latter useful for shorter tables and looking at lots of rows. Even better, Postgres can automatically detect which format is most ideal based on the width of the result set and your terminal. Setting \x auto turns on this automatic detection, so by default all my query results are cleanly formatted.
Setup your psqlrc today and enjoy psql as much as your shell
If you don’t already have a .psqlrc then today is the day to consider setting one up. There are a number of other great posts that talk about various configurations you can put into psqlrc, all are worth a read: