

Improving the Command-Line Postgres Experience - co_pl_te
http://robots.thoughtbot.com/improving-the-command-line-postgres-experience/

======
ultimoo
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` _directory_ to itself, which
was plain rude.

I'm largely new to PostgreSQL and it is quite possible that I'm using it all
wrong, sorry for ranting.

~~~
joevandyk
Yes, I also have a problem with createuser vs adduser vs useradd.

Also, dropdb, createdb, postgres, pg_ctl, initdb, pg_basebackup. No
consistency.

~~~
jeltz
I think it is a legacy issue. All the new executables are named with a pg_
prefix. Maybe someone should suggest a patch which renames the executables and
adds symlinks from the old names.

EDIT: There was a discussion and a patch 5 years ago, but I am not sure what
was the final result other than that it was obviously not changed.

[http://www.postgresql.org/message-
id/47EA5CC0.8040102@sun.co...](http://www.postgresql.org/message-
id/47EA5CC0.8040102@sun.com)

------
pjungwir
Here is my ~/.psqlrc:

    
    
        \pset pager
        \pset null 'NULL'
        \timing
        \encoding unicode
        SET search_path=public,postgis
    

The first line turns off paging, since I dislike how paging causes previous
queries/results to disappear after I press q.

The second line is like the article's.

`\timing` shows how long each query took to run.

I honestly don't remember why/if `\encoding unicode` was necessary. Playing
nice with my terminal? Writing to a file with `\o`?

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.

------
olefoo
If you haven't yet; set the following environment variables in your shell:

    
    
        export PAGER='less -S'
        export EDITOR='emacs -nw' #or a lesser editor if you prefer
    

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.

~~~
cbsmith
Might as well go whole hog with Emacs' SQL-mode and/or pg.el

~~~
olefoo
I like sql-mode but I also like doing things in the terminal. Using sql-mode
in emacs is for developing queries, but psql is what I use to talk to a
database of any consequence. A large part of that is so that I know what
context I'm in. Slightly more cautious and consequence oriented when it's the
production database with ten million rows of PII and less so when I'm
programming.

~~~
cbsmith
SQL-mode is a wrapper around psql that makes the psql tool a bit more human
friendly. You can always add a buffer name / theme for production to avoid
confusion. General rule of thumb: caution for production should take the form
of a more careful code release process (i.e., you release scripts that talk to
the database, not use psql directly). Executing interactive queries isn't for
prod.

~~~
olefoo
Oh dear; I fear you have gotten the wrong idea.

I spend most of my time in emacs doing things in python, sql or what have you.
Usually the only time I talk to a production database directly is when it's
already in trouble.

And yes; one's migrations and DDL etc. should all be tested before being
rolled out to production; apparently that is not an assumption people make
around here.

~~~
cbsmith
Apologies. I definitely got the wrong idea. Generally no one does ad hoc
queries to prod, even when in trouble. It's all, "sync to offline DB, check it
out there".

------
tbrock
I made something like this for MongoDB and even improved the api a bit.

[https://github.com/TylerBrock/mongo-
hacker](https://github.com/TylerBrock/mongo-hacker)

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.

