I have been thinking about the kinds of questions people have about Postgres if they’re mostly users of MySQL. One thing that comes up a lot is how to use the psql command-line.

I’m going to do a series of posts based on what I actually do every day with Postgres. This isn’t going to be an exhaustive look at all the features, but just the kinds of things I find useful.

Here’s a look at the kinds of commands I regularly use on a production system:

selena@wuzetian:~ #1642 15:13: awk '{print $1}' /tmp/cmds | uniq -c | sort -n -r 47 \e 22 \d 13 \x 12 \df+ 10 \q 9 \df 6 \ef 6 \d+ 5 \o 5 \h

Here’s the kinds of commands I use on my local system:

selena@wuzetian:~ #1645 15:15: awk '{print $1}' /tmp/local_cmds | uniq -c | sort -n -r 89 \d 43 \e 28 \df+ 14 \x 14 \d+ 13 \df 11 \c 10 \h 4 \a 3 \ef+

There’s not a whole lot of difference between the two. I pretty clearly use the database locally to look at schema definitions over and over again!

Here’s what each of these commands do:

\d+ : Examine a table, by default in 9.2 prints the table name, followed by the columns, their types, keys, indexes and constraints. The plus will cause all child tables that inherit from a parent to be listed.

: Examine a table, by default in 9.2 prints the table name, followed by the columns, their types, keys, indexes and constraints. The plus will cause all child tables that inherit from a parent to be listed. \e : Opens an editor defined by your EDITOR environment variable, and put the most recent command entered in psql into the buffer. You can define a non-command line editor here!

: Opens an editor defined by your EDITOR environment variable, and put the most recent command entered in into the buffer. You can define a non-command line editor here! \df+ : Prints information about a User Defined Function, including the function’s whole definition (that’s what the + does), best when combined with \x and probably \a as well

: Prints information about a User Defined Function, including the function’s whole definition (that’s what the does), best when combined with and probably as well \q : Quits psql . You can also quit with ^D

: Quits . You can also quit with \ef [function] : Opens up your editor, and puts the function into the buffer. Without a function, it provides a convenient template for creating a new function.

: Opens up your editor, and puts the function into the buffer. Without a function, it provides a convenient template for creating a new function. \o [filename] : Open a local file for writing the output of whatever commands you run next. Stop writing to the file with another \o

: Open a local file for writing the output of whatever commands you run next. Stop writing to the file with another \h : Help for SQL commands

: Help for SQL commands \c [databasename] : Connect to [databasename] on local database cluster

: Connect to [databasename] on local database cluster \a : Print output “unaligned”, or without adding whitespace to make columns align. Good when trying to print machine-readable output to the terminal.

: Print output “unaligned”, or without adding whitespace to make columns align. Good when trying to print machine-readable output to the terminal. \x : Print output “expanded”. This causes output to be printed out like: “Column: Value”, rather than the normal tabular/spreadsheet style. Useful in lots of contexts, especially when you’ve got some columns that have a very large text field.

And here’s a few useful commands that didn’t make the top 10 lists:

\? : Help for \ commands

: Help for commands \timing : Turn timing of all commands on, reports in ms.

: Turn timing of all commands on, reports in ms. \s : print out your psql history to STDOUT.

: print out your history to STDOUT. \i [filename] : execute the contents of [filename]

: execute the contents of [filename] \! [command] : execute a command in the local shell

Finally, when you start up psql, you have a few options. My favorite combination when generating machine-readable output is to add -AX -qt (axe cutie! hat tip to Greg Sabino Mullane for that mneumonic). Another very useful psql extension is -e , which causes the SQL commands used to produce output to also be printed out. This will help you learn about information_schema items and all the internal tables used to provide system information.