PostgreSQL on the Command Line | Hacker News Reader

PostgreSQL on the Command Line

phili.pe

254 pointsfphilipe43 comments

\e is the most amazing shortcut for big ugly queries!

I found that I often want to do basic things, like see the schema and some sample values, maybe even the distribution of values.

So, I ended up building a web UI, to do all of these in just a few clicks. It's also a good way for non-developers to run basic queries - https://getvql.com

It looks interesting, but there's no information on how I can take it out for a spin. Do you have any additional information, documentation or a screencast?

Screencast - https://getvql.com/demo_citi_bike.php

That's pretty cool. I'd like to see pricing before writing email, though.

As someone that is not a DBS expert, I like how easy it is to use PostgreSQL. Sure, you do need to conceptually know how a relational database works before you jump into it's interactive CLI. But, once you have that knowledge, you can learn a lot about a particular database by knowing just one command. That is '\?'.

That hasn't been my experience with MySQL and other SQL-flavors, nor with the NoSQL projects like MongoDB and CouchDB.

MySQL also had a usable commandline. Not sure it had shortcuts like \d+ etc though.

This is what MySQL gives you for \?:

  List of all MySQL commands:
  Note that all text commands must be first on line and end with ';'
  ?         (\?) Synonym for `help'.
  clear     (\c) Clear the current input statement.
  connect   (\r) Reconnect to the server. Optional arguments are db and host.
  delimiter (\d) Set statement delimiter.
  edit      (\e) Edit command with $EDITOR.
  ego       (\G) Send command to mysql server, display result vertically.
  exit      (\q) Exit mysql. Same as quit.
  go        (\g) Send command to mysql server.
  help      (\h) Display this help.
  nopager   (\n) Disable pager, print to stdout.
  notee     (\t) Don't write into outfile.
  pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
  print     (\p) Print current command.
  prompt    (\R) Change your mysql prompt.
  quit      (\q) Quit mysql.
  rehash    (\#) Rebuild completion hash.
  source    (\.) Execute an SQL script file. Takes a file name as an argument.
  status    (\s) Get status information from the server.
  system    (\!) Execute a system shell command.
  tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
  use       (\u) Use another database. Takes database name as argument.
  charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
  warnings  (\W) Show warnings after every statement.
  nowarning (\w) Don't show warnings after every statement.

  For server side help, type 'help contents'

I've always found MySQL's CLI vastly easier to use than PostgreSQL. The \G command alone is godsend. And the ASCII formatting makes everything much more readable to me.

have you tried "\x on" and "\x auto" in psql?

In MySQL you append \G at the end of the query instead of ; to get the same result as \x.

Weird I know, but it works. Still psql is a superior client IMHO.

yep. It's not the same at all. The format is harder to read, and the point of \G is you can do it without toggling. Say you run a query. Turns out, you need vertical format to see the entire contents. Just hit up arrow, replace the ';' at the end with '\G' and run the query again.

MySQL also has \c which I find tremendously useful, when you screw something up mid-query.

Here's a few, partially shameless plugs, of similar articles that have some overlap but hit some other niceties of psql.

http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/

http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...

Thanks Craig, in those 4 years I've been using Postgres extensively your blog posts, talks, newsletter [1], guide [2] have been invaluable learning resources. Now I'm trying to contribute back to the community, too :)

1: http://postgresweekly.com

2: http://www.postgresguide.com

Pgcli is an alternative command line tool with auto-completion and syntax highlighting. Open source and works well: http://pgcli.com/

Sure, you can use pgcli instead of psql. But it doesn't replace psql. In fact, most tasks described in the article cannot be accomplished with pgcli.

- it can't list schemas with \dn

- it can't list materialized views with \dm

- \dt <star>.users doesn't work as it does in psql, i.e. it just lists all tables due to the * instead of listing users tables in all schemas

- it can't use your editor with \e

- it can't input existing files with \i

- it can't redirect the output with \o

- it can't extract data with \copy

True, it's not a replacement for psql but a great little tool for examining databases and playing with queries.

Note that the project is quite new and has a really active dev base - if you use those features, feel free to submit a PR (or add an issue) and it's pretty sure to get accepted.

Has someone raised these yet? Otherwise I will.

Out of curiosity, I just tried out v0.19.2 (the latest), and some of these now work:

- \dn works

- \dm does not

- \dt <star>.users now appears to work as per psql

- \e works

- \i works

- \o does not

- \copy does not

Funny, I just tested those yesterday. And they none of them seemed to work. And I think I had installed the newest version for the test. Anyways, I stand corrected. It's great to see some of these working now as the tab-completion is definitely better than psql's.

So satisfying when you read an article that just happens to be pitched at exactly the stage of learning you're at. Every single bit of advice in here is something I can use today without any thought.

@fpilipe, you mention you use one schema per customer. I'm curious as to how many people do this and how well it works for them. Any thoughts?

It's not my usual way of doing things, but there is a Rails gem[1] for this that looks pretty nice.

[1] https://github.com/influitive/apartment

We use the apartment gem pjungwir mentioned. I guess it works well if you have a small number of customers, each with lots of data. The other way around, lots of customers with little data would not scale, I'd say. As with any technique, there's pros and cons.

Pros:

- When you enter a psql session, you `SET search_path TO customer;` and that's it. No customer_id column in any table you have to remember setting in your WHERE clause.

- Same thing for rails console. At the beginning of the session you choose which customer you want to operate on.

- Similar for web requests. You just activate the right customer (e.g. based on subdomain) and that's it. Instead of `current_customer.posts.find(params[:id])` you can now simply do `Post.find(params[:id])`.

- Data is completely separated. It's almost impossible to have a bug causing data to spill from one customer to the other, which is quite high chance when you always have to remember to load data through the current customer.

Cons:

- Number of objects (tables, indexes, sequences) is multiplied by number of customers.

- Adding a new customer requires `pg_dump` to be present as it dumps the public schema (the blueprint), creates a new schema, and loads the dump into that schema.

- Migrations take longer as there are more objects to touch.

In our case, the pros outweigh the cons. One further advantage of the separate schemas is that, in theory, we could at any time start using completely separate databases, one for each customer or just one additional for a much larger customer that's eating up all resources on the DB.

Thanks for that. Do you ever stagger releases to your customers? So some of the schemas are migrated to support new features before others?

That's technically possible, but we never did this. I feel like that's going to complicate things once you start doing this.

Semi-related: Has anyone had issues with psql wrapping long lines incorrectly? Specifically it starts overwriting the current line. I'm using zsh on OS X using the default terminal, though the behavior also appears when SSHing to a remote server which uses bash.

I found this: http://stackoverflow.com/questions/2024884/commandline-overw... but it didn't seem to help.

Same issue for me. Probably readline-related.

This only happens to me if I change the window size after launching psql. If I settle on a window size before launching it, then it never happens.

This only happens to me if I change the window size while viewing query results in less(1) while inside of psql. Resizing the windows at the psql prompt itself always works for me (and in fact fixes any issues caused by resizing while inside of less(1)).

Set your pager to let the overflow go off the screen:

    export PAGER='less -S'

And the lines will not wrap in the terminal, when you have a lot of output.

This here is some of the beauty of linux - psql simply pipes the output to less - a tool that is made exclusively to handle these sorts of "look at a bunch of text on a terminal" problems. Most of the problems less exists to solve would likely be deemed out of scope for a single-purpose utility like psql.

I use `less -SX`

$ shopt -s checkwinsize

on the bash command line before starting psql should fix this. This was one of those things I figured out several years after I started using bash that would have made my life much less annoying.

Thanks! Having to discuss that in 2015 is sad:

http://www.mail-archive.com/bug-bash@gnu.org/msg03562.html

https://blogs.oracle.com/dp/entry/why_bash_doesn_t_work

Great article, I love psql. I also add \timing to my .psqlrc to see how long every query took.

The reason I never made the switch to Postgres is that I haven't found a good tutorial that explains how to do the basic things I do with MySQL on the CLI with Postgres.

It starts with logging into the DBMS. Importing/exporting/dumping databases from/to a file? Running queries from the shell? User management? Backups?

Hi, just have a look here : http://phili.pe/posts/postgresql-on-the-command-line/

That's the link in this post so yeah, I read that. It covers most of the stuff that happens inside the Postgres CLI which is good, but I also need to know about the things I can do directly from the Linux shell.

How to backup a Postgres DB?

    mysqldump -u user -p DB > dump.sql

Import a DB from the Linux command line?

    mysql -u user -p DB < dump.sql

How to run a command directly from a shell script?

    mysql -u [user] -p[pass] -e "[mysql commands]"

When managing systems, this stuff is more important to me than the actual Postgres syntax.

Backup: http://www.postgresql.org/docs/current/static/app-pgdump.htm...

In short:

  pg_dump -Fc mydb > db.dump

Restore: http://www.postgresql.org/docs/current/static/app-pgrestore....

In short:

  pg_restore -C -d postgres db.dump

Command directly from the shell:

  psql -c "psql command"

and the default dumps are simple sql files, which you can actually pipe to another server.

pg_dump -C mydb | psql -h ${some_other_server} mydb

For CSV export and import from any DB including PostgreSQL, you should look at CSVKit for this purpose.

Import with csvsql http://csvkit.readthedocs.org/en/0.3.0/scripts/csvsql.html and export with sql2csv http://csvkit.readthedocs.org/en/latest/scripts/sql2csv.html

csvkit is a great tool, use it everyday. Haven't used the sql2csv tool though. For exporting, I'd say that psql2csv [1], which is mentioned in the article, is still easier to use. It has the same interface as psql, except for an additional argument, the query. And, it doesn't depend on python, which might not be available.

[1]: https://github.com/fphilipe/psql2csv

I'm a huge fan of JetBrains' 0xDBE, but nothing can replace knowing your command line magic. Very nice guide!

Reply on news.ycombinator.com