PostgreSQL array field with Django

One-dimensional arrays can be useful for indexing. See: GIN, and here's an example:

http://stackoverflow.com/a/4946888

This uses the same underlying indexing method often used with Full Text Search.

Values in arrays can be awkward if you are frequently pulling them out for joins, et al. However, if you frequently are dealing with operators over the entire collection they are quite handy, and I have enjoyed using them for this purpose.

An example that parallels something I've recently done for Real Work:

Suppose you are writing a piece of software that aggregates some blogs of various vendors and twitter feeds. You want to take a chunk of text (the contents of the post) and then be able to easily search for text that has a certain textual tag ascribed by their author. Sometimes, one's requirements are nicely met if one puts tags into an array and writes queries like this:

    SELECT contents FROM bucket_o_text WHERE tags && '{go,golang,gccgo}'

The "&&" is read "overlaps", so now you will get posts that seem to be about the programming language Go. If you want to find things about both golang and erlang, one might say:

    SELECT contents FROM bucket_o_text WHERE tags && '{go,golang,gccgo}'
    AND tags @> '{erlang}';

Where "@>" is read "contains". If you were confident the tags seem reasonably normalized, you might even write:

    SELECT contents FROM bucket_o_text WHERE tags @> '{erlang,golang}';

I would stop using this approach if I started having higher-order data about the tags 'erlang' and 'golang'. Said another way, data about tags, or metadata on tags, which are themselves metadata of the post. In this case I'd be inclined to break out 'erlang' and 'golang' into rows of a table so one could enumerate the concepts in the database easily and write constraints.

One advantage is filtering query results at the DB layer. For example, you could compare elements within an array, test a particular index of the array, or see if a value is present anywhere in the array. See http://www.postgresql.org/docs/9.2/static/arrays.html.

SQLAlchemy is an amazing library, and I wish I didn't need the Django Admin (our project is still depending on it for a bunch of... well, admin work) so I could just use SQLAlchemy everywhere.