This article is a sequel to our PostgreSQL series, which is aimed to teach you how to get the most out of your database. You might have been led by Active Record (or Rails in general) that it is a good idea to completely abstract away the database, but that it's hardly ever possible.

If you plan on switching databases on any larger-than-small application, you're going to have to do some manual work anyway. Every database is special and you should use it as such, instead of just settling for the lowest common denominator.

There are many features in PostgreSQL that can help you develop web applications, such as the array & json column types, hstore, PostGIS and much more. In this article we're going to take a look at arrays.

Every one of us remembers the moment when you first learned about databases and you were told that if you want to store multiple values in one column you have to split that in a 1:N relationship, because each column can only hold one value. Well, that's not true in the world of PostgreSQL. You are free to create array columns with arbitrary length. You can even perform array-like queries on them.

But first we need to start by creating a table. We'll do this with the most obvious example - posts with multiple tags. Each post has a title, content and an arbitrary number of tags. We also want to be able to select all posts with a specific tag.

Let's first create a new database so that we can play around and drop it at the end of out session, keeping our machine clean.

But let's stop here for a moment and take a look at our CREATE TABLE query in more depth. First we're defining our id column as SERIAL PRIMARY KEY. The PRIMARY KEY is pretty self explanatory, but what about the SERIAL attribute? If you come from the world of MySQL you probably know this as AUTO INCREMENT, but there's more to it.

Now you might be asking why in the world would you want to do it the more complicated way, instead of using just a simple SERIAL attribute. What if you wanted the id to increment by 2? Or start at a different index than 0. Well that's easy to do just by creating a custom SEQUENCE.

In our example code we're also defining a default value, in this case an empty array, which is defined as '{}' (the single quotes are important here). If we wanted a different default value, we can simply put it in the curly brackets '{thing}'. Note that these arrays columns can also be indexed and thus lighting fast:

CREATEINDEXidx_testonthingsUSINGGIN(names);

Now that we know the syntax, we can actually start inserting some data.

The advantage of doing this over just serializing the tags as a string (for example “apple,hamburger”) is that PostgreSQL knows it's an array and it can perform operations on it. We can easily select posts with a single tag.

One closing thought, it is important to think before using an array column. This is not a replacement for normalization and you should really think about the way you plan on using the data. If, for example, you wanted to create a dedicated page where you manage all of your tags, assign them to posts and so on, using an array column is probably not the best choice.

The following articles will cover things like JSON column types, hstore, migrating data from one type to another and much more.