Improving the Command-Line Postgres Experience

Slightly irrelevant but I have always had terrible experiences with PostgreSQL on the CLI. To start off, it has extremely generic names for its binaries that are installed on Linux -- `createuser` for example gives no indication that it handles user creation for PostgreSQL. This also means that I cannot do something like `pg-TAB-TAB` to find out all PostgreSQL relevant binaries that I can then invoke. Apart form this, the OS X PostgreSQL installer I noticed had changed the ownership of the `&#x2F;usr&#x2F;local&#x2F;bin` <i>directory</i> to itself, which was plain rude.<p>I&#x27;m largely new to PostgreSQL and it is quite possible that I&#x27;m using it all wrong, sorry for ranting.

Here is my ~&#x2F;.psqlrc:<p><pre><code> \pset pager \pset null &#x27;NULL&#x27; \timing \encoding unicode SET search_path=public,postgis </code></pre> The first line turns off paging, since I dislike how paging causes previous queries&#x2F;results to disappear after I press q.<p>The second line is like the article&#x27;s.<p>`\timing` shows how long each query took to run.<p>I honestly don&#x27;t remember why&#x2F;if `\encoding unicode` was necessary. Playing nice with my terminal? Writing to a file with `\o`?<p>The `search_path` line is because I often install PostGIS objects in a `postgis` schema, and this lets me reference them without a prefix. If there is no `postgis` schema I get a warning when I start psql, but that&#x27;s no bother to me.

If you haven&#x27;t yet; set the following environment variables in your shell:<p><pre><code> export PAGER=&#x27;less -S&#x27; export EDITOR=&#x27;emacs -nw&#x27; #or a lesser editor if you prefer </code></pre> That way you&#x27;ll get result sets without linewraps and your queries will be edited in the non-default editor, which is `vi` in most distributions.

I made something like this for MongoDB and even improved the api a bit.<p><a href="https://github.com/TylerBrock/mongo-hacker" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;TylerBrock&#x2F;mongo-hacker</a><p>I found that adding color to the JSON output helped newcomers differentiate the data types and understand the structure of the data models more easily.