On Mon, Jul 30, 2012 at 5:00 PM, Leon Smith <leon.p.sm...@gmail.com> wrote: > 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 _______________________________________________ database-devel mailing list database-devel@haskell.org http://www.haskell.org/mailman/listinfo/database-devel