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 `/usr/local/bin` <i>directory</i> to itself, which was plain rude.<p>I'm largely new to PostgreSQL and it is quite possible that I'm using it all wrong, sorry for ranting.
Here is my ~/.psqlrc:<p><pre><code> \pset pager
\pset null 'NULL'
\timing
\encoding unicode
SET search_path=public,postgis
</code></pre>
The first line turns off paging, since I dislike how paging causes previous queries/results to disappear after I press q.<p>The second line is like the article's.<p>`\timing` shows how long each query took to run.<p>I honestly don't remember why/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's no bother to me.
If you haven't yet; set the following environment variables in your shell:<p><pre><code> export PAGER='less -S'
export EDITOR='emacs -nw' #or a lesser editor if you prefer
</code></pre>
That way you'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://github.com/TylerBrock/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.