Introduction to psql

PostgreSQL is an awesome piece of software. Superior in almost every way to MySQL and many of the commercial database options out there. Though I likely am abnormal in this – it and psql (its command-line interface) are literally some of my favorite things. To drive this home, here is the list of my top 5 commands based on my recent history at this moment.

1. ssh (110)
2. ls (93)
3. cd (76)
4. emacs (35)
5. psql (28)

By the way, to figure out your top commands give this a try.

history | awk '{print $2}' | awk 'BEGIN {FS="|"}{print $1}' | sort | uniq -c | sort -n | tail | sort -nr

Notice that psql cracks the top 5. As already stated, it is the CLI for PostgreSQL and it is fantastic. The goal of this post is to provide a quick introduction to using psql. I won’t be talking about PostgreSQL the database – the official docs are sufficient for almost any question you may have and the mailing lists will fill in any gap. This is a very brief introduction.

The Ultimate Admin Interface

Who needs to build admin interfaces when you have psql and SQL?

In general psql is all about being efficient and using minimal keystrokes.

To view the list of available databases simply run psql -l from the command line. To then connect to a database run psql [database] (e.g. psql startups).

To connect as a specific user use psql [database] -U [user] (e.g. psql startups -U joe).

Once connected you can start writing queries and issuing commands. The basics:

• \q – quit
• \? – help with psql commands
• \help – help covering all available SQL language constructs
• \d – list of tables in current database
• \d [table] (e.g. \d people) – list of a table’s columns and indexes

Each query you execute is wrapped in its own transaction. You can custom control the duration of a transaction however you like by manually typing BEGIN and then either COMMIT or ROLLBACK. This is generally a VERY good idea when running commands against a production server. PostgreSQL is amazing so you can do almost anything you want in a transaction and roll it back if you mess up. An example.

BEGIN;
delete from people;
drop table companies;
alter table people rename first_name to nickname;
ROLLBACK;

The rollback undoes everything. In general you should be nervous and cautious when not in a transaction (the all-the-time state in MySQL’s default) and happy and safe when in a transaction.

Getting Data Out

If you need to get data out of your database and don’t just want a raw dump using pg_dump, psql has some great commands for doing that. The ones I use all the time follow. I do this extensively for producing custom reports.

• \a – toggles unaligned mode meaning just data-delimiter-data, no spaces or junk to make it print nicely
• \f [character] (e.g. \f ,) – changes delimiter to the provided character
• \o [filename] (e.g. \o my_data.txt) – specifies a file to which all output should be redirected instead of printing it to stdout

You can combine the above to get whatever data or report you need. As an example, say I need the first and last name of everyone in the database. The sequence of commands once inside psql would be something like this.

\a
\f ,
\o engineers.csv
select first_name, last_name from people;
\q

The result is a comma-delimited file named engineers.csv written to your current directory. I use this a lot, you can have it generate HTML files as well.

Even Faster

psql can do a lot from the terminal without even having to enter interactive mode. Run psql --help for a full list. The most common scenario is wanting to run a query and get the output instead of having it printed out in interactive mode. For this use the -c flag. A simple example:

psql -c "select * from startups"

That example above of generating a file could have also been achieved this way by using a few more flags:

psql -A -F , -o engineers.csv -c "select first_name, last_name from people"

Here is a handy script that combines the ‘watch’ command with psql to produce an auto-updating display of currently running queries in a database.

watch -n 2 -d 'psql wildfire -c "select waiting, procpid, query_start, current_query from pg_stat_activity where current_query != '\''<IDLE>'\'' order by query_start" | grep -v "select waiting"'

It works by pulling the relevant information from the pg_stat_activity system table, removing lines that are just idle connections (not idle in transaction which is different and generally bad) and then watches that output, updating every 2 seconds.

Conclusion

That was a super quick introduction and only covered a few bits but honestly the above is the bulk of what I need on a daily basis (along with a good understanding of SQL and PostgreSQL’s planner). As with the PostgreSQL documenation the psql and SQL documentation accessible from within psql is incredibly good and thorough so feel free to search around in there if you need to do something specific.