[database-devel] Arrays and postgresql-simple

[Joey Adams]

On Mon, Jul 30, 2012 at 5:00 PM, Leon Smith <leon.p.smith at 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