Tips for psql

I spend a lot of time in psql. Before becoming a developer I was in business intelligence, where I spent much of my days writing queries, and pulling data from psql. After switching to rails I still find it much easier to check out data and play with queries via psql directly rather than using the rails console. Here are a few tips and tricks that may be common knowledge to some, but I wish I had known when I first started. I will be using a database I created of all the bus stops in Chicago for this post.

Expanded Display

\x toggles the expanded display in psql. It is defaulted to off when you first start, so if you want to look at one record you will get something like this:

[local] [email protected]=# select * from bus_stops limit 1;
 id | on_street | cross_street | routes | boardings | alightings | month_beginning | daytype |          location           
----+-----------+--------------+--------+-----------+------------+-----------------+---------+-----------------------------
  1 | JACKSON   | AUSTIN       | 126    |     183.4 |        150 | 2012-10-01      | Weekday | (41.87632184, -87.77410482)
(1 row)

This is ok if you only have a few columns, but for a table with a lot of columns, you will start to see a jumbled output as the record covers multiple lines. Lets try it after toggling the expanded display.

[local] [email protected]=# \x
Expanded display is on.
[local] [email protected]=# select * from bus_stops limit 1;
-[ RECORD 1 ]---+----------------------------
id              | 1
on_street       | JACKSON
cross_street    | AUSTIN
routes          | 126
boardings       | 183.4
alightings      | 150
month_beginning | 2012-10-01
daytype         | Weekday
location        | (41.87632184, -87.77410482)

Very nice, and for me, easier to digest. The best of both worlds is to make your psql pick whether to use the expanded display or not based on your window size and what is being returned. Luckily you can do this by creating a .psqlrc dotfile and adding the following line to it.

\x auto
Control + R

Control + R searches through the history of your previous psql commands. You can use Control + R right in your normal shell, and irb for that matter, for the same purpose (which I highly recommend, its probably my favorite feature), but many people don't know it works in psql as well.

[local] [email protected]=# select * from bus_stops where boardings > 150 limit 2;
 id | on_street | cross_street | routes | boardings | alightings | month_beginning | daytype |          location           
----+-----------+--------------+--------+-----------+------------+-----------------+---------+-----------------------------
  1 | JACKSON   | AUSTIN       | 126    |     183.4 |        150 | 2012-10-01      | Weekday | (41.87632184, -87.77410482)
 14 | JACKSON   | CICERO       | 126    |     530.5 |      145.2 | 2012-10-01      | Weekday | (41.87676314, -87.74483066)
(2 rows)

Time: 0.364 ms

Days pass, many psql commands are done, and you want to pull up that query without hitting the up key 100 times? Just use Control + R and start typing something you remember was in the query. If something else pops up just keep hitting Control + R until you get the right one as it will cycle through the list.

(reverse-i-search)`where board': select * from bus_stops where boardings > 150 limit 2;
[local] [email protected]=# select * from bus_stops where boardings > 150 limit 2;
 id | on_street | cross_street | routes | boardings | alightings | month_beginning | daytype |          location           
----+-----------+--------------+--------+-----------+------------+-----------------+---------+-----------------------------
  1 | JACKSON   | AUSTIN       | 126    |     183.4 |        150 | 2012-10-01      | Weekday | (41.87632184, -87.77410482)
 14 | JACKSON   | CICERO       | 126    |     530.5 |      145.2 | 2012-10-01      | Weekday | (41.87676314, -87.74483066)
(2 rows)
\copy

Copy and pasting out of psql can be quite a hassle if there are a lot of rows. You need to first hit the space bar until they are all loaded, and then highlight and scoll up until you can copy them all. It is much easier to just copy the output in the format that you want, and then do what you will with the file. This can be combined with the \a command to get rid of those annoying white spaces that come with copy pasting out of psql.

[local] [email protected]=# \a
Output format is unaligned.
[local] [email protected]=# \copy (select * from bus_stops where boardings > 150) to 'boardings_over_150.csv' csv header
COPY 1830
Time: 10.197 ms

I now have that csv file in the directory I was in when I entered psql.

Table instead of Select *

This tip is certainly not revolutionary but it does save a few keystrokes. Instead of select * from bus_stops. You can use table bus_stops

[local] [email protected]=# table bus_stops limit 5;
 id | on_street |    cross_street     | routes | boardings | alightings | month_beginning | daytype |          location           
----+-----------+---------------------+--------+-----------+------------+-----------------+---------+-----------------------------
  1 | JACKSON   | AUSTIN              | 126    |     183.4 |        150 | 2012-10-01      | Weekday | (41.87632184, -87.77410482)
  2 | JACKSON   | MAYFIELD (EXTENDED) | 126    |       5.3 |        0.2 | 2012-10-01      | Weekday | (41.87706679, -87.77131794)
  3 | JACKSON   | MENARD              | 126    |       8.3 |        0.7 | 2012-10-01      | Weekday | (41.87695725, -87.76975039)
  4 | JACKSON   | 5700 WEST           | 126    |      17.9 |          3 | 2012-10-01      | Weekday | (41.87702418, -87.76745055)
  6 | JACKSON   | LOTUS               | 126    |        74 |       11.2 | 2012-10-01      | Weekday | (41.87651300, -87.76144600)
(5 rows)

Thats 7 keystrokes saved! That can add up if you often find yourself querying whole tables. Unfortunately this does not work with queries with joins or where clauses, so just use it when you are really just pulling a raw table.

Thats all I've got for now - let me know in the comments if you have any others that you think I should know.

-JM

comments powered by Disqus