{"id":5058,"date":"2013-11-06T15:39:02","date_gmt":"2013-11-06T23:39:02","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=5058"},"modified":"2013-11-13T13:01:10","modified_gmt":"2013-11-13T21:01:10","slug":"top-10-psql-commands-i-use","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2013\/11\/06\/top-10-psql-commands-i-use\/","title":{"rendered":"Everyday Postgres: Top 10 psql &#8216;\\&#8217; commands I use"},"content":{"rendered":"<p>I have been thinking about the kinds of questions people have about Postgres if they&#8217;re mostly users of MySQL. One thing that comes up a lot is how to use the <a href=\"http:\/\/www.pgcon.org\/2009\/schedule\/attachments\/116_Power_psql.ppt\">psql<\/a> command-line.<\/p>\n<p>I&#8217;m going to do a series of posts based on what I actually do every day with Postgres. This isn&#8217;t going to be an exhaustive look at all the features, but just the kinds of things I find useful.<\/p>\n<p>Here&#8217;s a look at the kinds of commands I regularly use on a production system:<\/p>\n<pre><code>selena@wuzetian:~ #1642 15:13: awk '{print $1}' \/tmp\/cmds   | uniq -c | sort -n -r\n     47 \\e\n     22 \\d\n     13 \\x\n     12 \\df+\n     10 \\q\n      9 \\df\n      6 \\ef\n      6 \\d+\n      5 \\o\n      5 \\h\n<\/code><\/pre>\n<p>Here&#8217;s the kinds of commands I use on my local system:<\/p>\n<pre><code>selena@wuzetian:~ #1645 15:15: awk '{print $1}' \/tmp\/local_cmds  | uniq -c | sort -n -r\n     89 \\d\n     43 \\e\n     28 \\df+\n     14 \\x\n     14 \\d+\n     13 \\df\n     11 \\c\n     10 \\h\n      4 \\a\n      3 \\ef+\n<\/code><\/pre>\n<p>There&#8217;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!<\/p>\n<p>Here&#8217;s what each of these commands do:<\/p>\n<ul>\n<li><code>\\d+<\/code>: 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.<\/li>\n<li><code>\\e<\/code>: Opens an editor defined by your EDITOR environment variable, and put the most recent command entered in <code>psql<\/code> into the buffer. You can define a non-command line editor here!<\/li>\n<li><code>\\df+<\/code>: Prints information about a User Defined Function, including the function&#8217;s whole definition (that&#8217;s what the <code>+<\/code> does), best when combined with <code>\\x<\/code> and probably <code>\\a<\/code> as well<\/li>\n<li><code>\\q<\/code>: Quits <code>psql<\/code>. You can also quit with <code>^D<\/code><\/li>\n<li><code>\\ef [function]<\/code>: Opens up your editor, and puts the function into the buffer. Without a function, it provides a convenient template for creating a new function.<\/li>\n<li><code>\\o [filename]<\/code>: Open a local file for writing the output of whatever commands you run next. Stop writing to the file with another <code>\\o<\/code><\/li>\n<li><code>\\h<\/code>: Help for SQL commands<\/li>\n<li><code>\\c [databasename]<\/code>: Connect to [databasename] on local database cluster<\/li>\n<li><code>\\a<\/code>: Print output &#8220;unaligned&#8221;, or without adding whitespace to make columns align. Good when trying to print machine-readable output to the terminal.<\/li>\n<li><code>\\x<\/code>: Print output &#8220;expanded&#8221;. This causes output to be printed out like: &#8220;Column: Value&#8221;, rather than the normal tabular\/spreadsheet style. Useful in lots of contexts, especially when you&#8217;ve got some columns that have a very large text field.<\/li>\n<\/ul>\n<p>And here&#8217;s a few useful commands that didn&#8217;t make the top 10 lists:<\/p>\n<ul>\n<li><code>\\?<\/code>: Help for <code>\\<\/code> commands <\/li>\n<li><code>\\timing<\/code>: Turn timing of all commands on, reports in ms.<\/li>\n<li><code>\\s<\/code>: print out your <code>psql<\/code> history to STDOUT.<\/li>\n<li><code>\\i [filename]<\/code>: execute the contents of [filename]<\/li>\n<li><code>\\! [command]<\/code>: execute a command in the local shell <\/li>\n<\/ul>\n<p>Finally, when you start up psql, you have a few options. My favorite combination when generating machine-readable output is to add <code>-AX -qt<\/code> (axe cutie! hat tip to <a href=\"http:\/\/www.gtsm.com\/\">Greg Sabino Mullane<\/a> for that mneumonic). Another very useful psql extension is <code>-e<\/code>, which causes the SQL commands used to produce output to <em>also<\/em> be printed out. This will help you learn about <code>information_schema<\/code> items and all the internal tables used to provide system information.<\/p>\n<p>The shortcuts really worth spending a bit of time exploring are <code>\\e*<\/code> and <code>\\d*<\/code>. Both provide quite a bit of useful functionality, with relatively easy to remember letter combinations.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I have been thinking about the kinds of questions people have about Postgres if they&#8217;re mostly users of MySQL. One thing that comes up a lot is how to use the psql command-line. I&#8217;m going to do a series of &hellip; <a href=\"https:\/\/www.chesnok.com\/daily\/2013\/11\/06\/top-10-psql-commands-i-use\/\">Continue reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[604],"class_list":["post-5058","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-everyday-postgres"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5058","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=5058"}],"version-history":[{"count":8,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5058\/revisions"}],"predecessor-version":[{"id":5087,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5058\/revisions\/5087"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=5058"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=5058"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=5058"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}