Last edit
Changed:
< CategoryEnglish, CategoryBlog
to
> CategoryEnglish, CategoryBlog, CategoryPostgreSQL
As I mentioned many times, I am a PostgreSQL fan. I wrote several times about psql, the default PostgreSQL terminal client. A few days ago I gave a short talk in my company about the virtues of psql, and in preparation for that I looked at its manual (again). As is often the case, I discovered a few hidden gems I didn’t know about.
psql has quite a few “backslash commands” or “metacommands”. Many of them operate on the “current query buffer”, which means what you have typed so far but haven’t yet sent to the server for execution. For example, if you type this:
select * from my_table-!-
where -!- means the cursor, and then type \g followed by Enter, the metacommand \g will treat the select * from table as its input. However, if the current query buffer is empty, \g will use the previous query.
Since \g basically just sends its input to the server, you can use it instead of the semicolon to end your query. It is more capable than that, though. For example, you can follow it by a filename, and then it will output the result of your query to that file. Alternatively, you can use a pipe symbol followed by a shell command to pass the result to the given command. And if that were not enough, you can follow \g by a list of options and their values in parenthesis. This way, the query will be run with those options set to the given values, as if you invoked \pset before, but the options will only be valid for this single query. For instance, you can generate a file with a LaTeX table, without the annoying vertical lines, and see its first few lines with this invocation:
\g (format=latex border=0) |tee /tmp/table.tex | head -n2
Now, if you remember my troubles with \copy and multiline SQL statements, here is a much simpler way to generate a CSV file from the result of a (possibly multiline) select statement:
select * from my_table \g (format=csv) my-table.csv
And you can also invoke \g after you successfully ran the select and saw that its results are exactly what you want.
Another trick I learned (or relearned) is the \! metacommand. It allows you to run any shell command without leaving psql. For example, if you want to check the current directory, you can say \!
pwd. You can also type \! without any argument, which then runs an interactive shell; when you exit the shell, you get back to your psql prompt.
There is also the \set command, which – surprise! – sets (psql) variables. These variables are a very useful concept. For example, assume that you have some uuid you use all the time, say 9e7c2a24-42bb-4c1c-9479-933ec8d30fd1. Instead of copying and pasting it time and time again, you can say \set uuid_var
'9e7c2a24-42bb-4c1c-9479-933ec8d30fd1' and then use it like this:
select * from some_table where some_uuid_column = :'uuid_var';
to get the data about the row with the given uuid. (You can also have a column or table name in some variable and then say things like :"column_var".)
But it gets better! Oftentimes the value(s) you need can be read from some table. For example, let’s assume that you have a user table with columns like user_id, user_full_name or user_email. If you need to query some other tables using (for example) one user’s id and email, you can put them into variables using the \gset metacommand.
select user_id, user_email as email from "user" where user_full_name = 'John Doe' \gset
and you’ll set the variables user_id and email to the uuid and email of John Doe. (In order for this to work, the query must return exactly one row.) And if you do this a lot, you might want to know that the \gset metacommand takes one optional “prefix” parameter, so \gset p_ will prepend p_ to the names of the variables it creates.
The last thing I’d like to mention is one of the most complicated psql metacommands, \crosstabview. Assume that you have a query that displays e.g. some counts, broken down by two variables. Let’s take an example from Wikipedia:
select 'male' as sex, 'right' as handedness, 43 as count union select 'male' as sex, 'left' as handedness, 9 as count union select 'female' as sex, 'right' as handedness, 44 as count union select 'female' as sex, 'left' as handedness, 4 as count
If you just evaluate it using \g (or a semicolon), you get this table:
sex | handedness | count --------+------------+------- male | left | 9 female | left | 4 male | right | 43 female | right | 44 (4 rows)
But if you use \crosstabview instead of \g, you get the following contingency table:
sex | left | right --------+------+------- male | 9 | 43 female | 4 | 44 (2 rows)
How cool is that‽ Head on to the manual to learn the details (the argumentless invocation of \crosstabview works if the query result has exactly three columns – if it has more, you need to tell psql which column to use as the vertical and horizontal headers and the output).
That’s it for today. Happy querying!