Improving the Command-Line Postgres Experience | Hacker News Reader

Improving the Command-Line Postgres Experience

robots.thoughtbot.com

58 pointsco_pl_te13 comments

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.

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

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

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...

I can't say that I've actually used any of those. You can do the same with CREATE [USER|SCHEMA|DATABASE] quite happily and ignore the rest.

I tend not to use the CLI though as anything that presents a readline interface is a POS to work with when you have a verbose statement to type in. I'll type it out in VIM then throw the buffer through psql.

I wouldn't run anything on OSX bar some Cocoa apps myself. Same with Windows. Everything Unixy happens inside a virtual machine. I don't care if OSX is Unix certified or Windows has cygwin - they're both a pain to deal with on a daily basis.

Using \e within the psql shell will drop you right into your $EDITOR, which is likely vim. You can edit commands in there directly and they will be evaluated when you :wq out. The next time you type \e it will load the previous command back into the editor

My only issue with this workflow is when I want to yank the command out to the clipboard. For some reason it doesn't seem to respect clipboard settings on OS X.

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.

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.

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

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.

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.

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.

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".

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

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.

Reply on news.ycombinator.com