4 ms·

Improving the Command-Line Postgres Experience

58 points by co_pl_te 13y ago

olefoo 13y ago
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 13y ago
  Might as well go whole hog with Emacs' SQL-mode and/or pg.el
  - olefoo 13y ago
    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 13y ago
      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 13y ago
        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 13y ago
        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".
ultimoo 13y ago
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 13y ago
  Yes, I also have a problem with createuser vs adduser vs useradd. Also, dropdb, createdb, postgres, pg_ctl, initdb, pg_basebackup. No consistency.
  - jeltz 13y ago
    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.com http://www.postgresql.org/message-id/47EA5CC0.8040102@sun.co...
- csmuk 13y ago
  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.
  - 13y ago
pjungwir 13y ago
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.
tbrock 13y ago
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.
deleted 13y ago
[deleted]

58 points by co_pl_te 13y ago

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 13y ago
Might as well go whole hog with Emacs' SQL-mode and/or pg.el
- olefoo 13y ago
  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 13y ago
    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 13y ago
      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 13y ago
        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".

ultimoo 13y ago

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 13y ago
Yes, I also have a problem with createuser vs adduser vs useradd. Also, dropdb, createdb, postgres, pg_ctl, initdb, pg_basebackup. No consistency.
- jeltz 13y ago
  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.com http://www.postgresql.org/message-id/47EA5CC0.8040102@sun.co...
csmuk 13y ago
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.
- 13y ago

pjungwir 13y ago

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.

tbrock 13y ago

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.

deleted 13y ago

[deleted]