On Mon, Jul 30, 2012 at 5:00 PM, Leon Smith
Now, presumably, postgresql does actually support arrays of arrays, but I'm guessing that you need to create the array of array of string type before you can actually run this query...
Actually, PostgreSQL does not truly support the notion of "arrays of arrays". PostgreSQL arrays are actually N-dimensional matrices, where N is a number from 1 to 6. For example, '{{1,2},{3,4}}' is a valid array, but '{{1,2},{3}}' is not (inconsistent dimensions). Some other weird things about PostgreSQL arrays: * None of an array's dimensions may be zero. For example, '{{},{}}' is invalid. However, an empty, dimensionless array is allowed: '{}'. * Many aggregate functions, such as array_agg, return null instead of an empty array when given an empty set. Care must be taken when using array_agg to construct an array. * Not every type uses ',' as the delimiter character (but almost all of them do). For example, the box type uses ';' instead, as it uses ',' to delimit coordinates. The typdelim column in pg_type [1] indicates what delimiter a given type uses. * PostgreSQL arrays can have explicit dimensions: > SELECT '[5]={1,2,3,4,5}'::int[]; int4 ------------- {1,2,3,4,5} > SELECT '[3:5]={3,4,5}'::int[]; int4 --------------- [3:5]={3,4,5} > SELECT '[2][3]={{1,2,3},{4,5,6}}'::int[]; int4 ------------------- {{1,2,3},{4,5,6}} As you can see, PostgreSQL arrays are perilous. They're useful nonetheless. I would benefit from postgresql-simple having basic array support. It would be one less obstacle to switching my application to use postgresql-simple instead of the crummy DB code I wrote. In that crummy code, I have a function for reading a PostgreSQL array. It supports explicit dimensions, NULL, and unescaping (array items can be quoted with double quotes, and may contain backslash escapes). However, it only supports single-dimensional arrays, and I have not written a corresponding function to generate a PostgreSQL array. Want me to clean up my array parsing code and post it? Thanks, -Joey [1]: http://www.postgresql.org/docs/current/static/catalog-pg-type.html