Not bad, for one query we’ve taken our three rows, and turned them into a string, without having to use anything other than SQL! There’s just one final problem here that I’d like to fix, namely that when you invoke a function as we did here, the function’s name becomes the name of the column. WHERE meeting_at::date = '2014-may-23'ORDER BY meeting_at), ', ') Īnd sure enough, the result is as follows: +-+ This function, wrapped around a call to ARRAY, wrapped around our subselect, will then look like: SELECT array_to_string(ĪRRAY(SELECT meeting_at::time || ' ' || description Fortunately, PostgreSQL’s array_to_string can save the day, acting similarly to the “join” method that I know and love from such languages as Ruby and Python. But earlier, I said that I want to have a comma-separated list of the appointment. I can then check the array’s contents, length, or anything else that might be useful in my analysis.

I do this all of the time when creating reports or summaries of other data: I write a view or CTE that grabs the data from the rows that are of interest to me, and stuffs that data into an array. You can see that our three rows have been turned into three elements of the array. The outer one is just our call to ARRAY: SELECT ARRAY(SELECT meeting_at::time || ' ' || descriptionĪnd sure enough, we get a one-row, one-column result: +-+ Thus, using ARRAY means that we’re using a subselect. Now, the ARRAY function needs to be invoked via a SELECT. Now that I have my rows, I can turn them into an array. My query looks like: SELECT meeting_at::time || ' ' || description I’m going to use PostgreSQL’s concatenation operator (||) to combine the time (using the ::time cast) of the appointment with the appointment itself. Let’s start with a set of rows, containing the descriptions of today’s appointments. That is, ARRAY expects to get rows as its input, and returns a single row, containing a single array, as its output. The ARRAY function is built into PostgreSQL, and turns a set of rows into an array. parentheses that tell it to do different things.) (To be honest, I’m not sure how PostgreSQL can tell the difference it might be the square brackets vs. Now, don’t confuse the ARRAY constructor with the ARRAY function, although it’s hard not to. The way to accomplish this is with the ARRAY function. With PostgreSQL’s arrays, though, we can do this all in the database - which executes much more quickly, among other things.

Normally, I would need to retrieve all of the rows, and then use another language to piece them together. But let’s say that I want to get all of the descriptions of today’s appointments in a comma-separated list. So far, this isn’t very exciting or surprising. Without that, the comparison would have been at midnight. Notice that in the above example, I’m using PostgreSQL’s casting operator (::) to turn the meeting_at column, which contains a TIMESTAMP value, into a DATE value. If I want to find out what I’m doing on May 23rd, I can simply say: /reuven=# select * from appointments where meeting_at::date = '2014-may-23' I create an Appointments table: CREATE TABLE Appointments (id SERIAL,Īnd then I enter information into this table, such that I have the following appointments: /reuven=# select * from appointments order by meeting_at This is where, in my mind, arrays start to get truly powerful, and allow us to do all sorts of things that would otherwise be quite difficult, or require another language.įor example, let’s say that I want to keep track of my appointments in a database. But the coolest trick, or set of tricks, that I use in PostgreSQL is the ability to turn arrays into rows, and vice versa. So far in this series, I have described how you can create arrays and retrieve information from them - both the actual data stored in the array, and information about the array, such as its length.

Postgresql array functions