Greater than with postgres int array columns

21 Jul 2015

Introduction

One of my favorite parts about Postgres is that you can have array columns. Either text array or integer arrays.
This is very useful for querying data without joining.

Models

This is a pretty common model and table design, you have a model representing a schedule and a model representing the actual events.
For example: “Hacking with Avi” has multiple schedules in the following couple of days at different venues with different capacity for attendents.

Querying

The default approach for querying this will be to join the Event with the EventSchedule and query the scheduled_on column.
However, my prefered approach would be to cache the scheduled_on column on the Event table.

I am adding a column called schedules to the Event table, that column is of type integer[] with default to [].

Lets take this ruby code for example here:

      event.schedules = event.schedules.collect { |schedule| schedule.scheduled_on.to_i }

This will give us something like this:

[1438059600, 1438619400, 1437973200, 1438014600, 1438578000, 1438664400]

Notice that I am converting the date into an integer.

If you read about Array functions in potgres you see that it’s not really trivial to query for greater than on the array elements.

The intarray module provides a bit more usefulness in the function it provides but still doesn’t provide what I really need.

The solution

The solution turns out to be pretty simple.

Lets say you have a date in integer form 1437497413 you can do this

select name from events where 1437497413 < any(schedules);

Bonus

One of the other things that is very common when you are working with integer array is sorting by one of the elements (either the min or the max).

For example, I want to sort by the dates.

Here’s what you can do

select (sort(schedules))[1] min_date from events  where 1437453975 < any(schedules) order by min_date DESC;

Conclusion

You can see here, it’s pretty easy to manipulate and query array elements in Postgres, I encourage you to embrace the power of it and use it in your application, it will scale better and will make the data modeling easier for you.

Questions? Feedback?

Questions? Feedback? Feel free to discuss in the comments