
PostgreSQL on the Command Line - fphilipe
http://phili.pe/posts/postgresql-on-the-command-line/
======
jastr
\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](https://getvql.com)

~~~
shrikant
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?

~~~
jastr
Screencast -
[https://getvql.com/demo_citi_bike.php](https://getvql.com/demo_citi_bike.php)

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

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

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

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

------
craigkerstiens
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/21/more-out-of-psql/)

[http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-
Pos...](http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/)

~~~
fphilipe
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](http://postgresweekly.com)

2: [http://www.postgresguide.com](http://www.postgresguide.com)

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

~~~
fphilipe
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

~~~
thelibrarian
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

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

------
aidos
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?

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

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

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

------
tcas
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...](http://stackoverflow.com/questions/2024884/commandline-overwrites-
itself-when-the-commands-get-to-long) but it didn't seem to help.

~~~
narrator
$ 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.

~~~
acqq
Thanks! Having to discuss that in 2015 is sad:

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

[https://blogs.oracle.com/dp/entry/why_bash_doesn_t_work](https://blogs.oracle.com/dp/entry/why_bash_doesn_t_work)

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

------
yAnonymous
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?

~~~
MitsuTomoe
Hi, just have a look here : [http://phili.pe/posts/postgresql-on-the-command-
line/](http://phili.pe/posts/postgresql-on-the-command-line/)

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

~~~
AlterEgo20
Backup: [http://www.postgresql.org/docs/current/static/app-
pgdump.htm...](http://www.postgresql.org/docs/current/static/app-pgdump.html)

In short:

    
    
      pg_dump -Fc mydb > db.dump
    

Restore: [http://www.postgresql.org/docs/current/static/app-
pgrestore....](http://www.postgresql.org/docs/current/static/app-
pgrestore.html)

In short:

    
    
      pg_restore -C -d postgres db.dump
    

Command directly from the shell:

    
    
      psql -c "psql command"

~~~
y4mi
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

------
ThomasG77
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](http://csvkit.readthedocs.org/en/0.3.0/scripts/csvsql.html)
and export with sql2csv
[http://csvkit.readthedocs.org/en/latest/scripts/sql2csv.html](http://csvkit.readthedocs.org/en/latest/scripts/sql2csv.html)

~~~
fphilipe
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](https://github.com/fphilipe/psql2csv)

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

