sql - PostgreSQL - best way to return an array of key-value pairs

Question

I'm trying to select a number of fields, one of which needs to be an array with each element of the array containing two values. Each array item needs to contain a name (character varying) and an ID (numeric). I know how to return an array of single values (using the ARRAY keyword) but I'm unsure of how to return an array of an object which in itself contains two values.

The query is something like

SELECT
    t.field1,
    t.field2,
    ARRAY(--with each element containing two values i.e. {'TheName', 1 })
FROM MyTable t

I read that one way to do this is by selecting the values into a type and then creating an array of that type. Problem is, the rest of the function is already returning a type (which means I would then have nested types - is that OK? If so, how would you read this data back in application code - i.e. with a .Net data provider like NPGSQL?)

Any help is much appreciated.

Answer 1

ARRAYs can only hold elements of the same type

Your example displays a text and an integer value (no single quotes around 1). It is generally impossible to mix types in an array. To get those values into an array you have to create a composite type and then form an ARRAY of that composite type like you already mentioned yourself.

Alternatively you can use the data types json in Postgres 9.2+, jsonb in Postgres 9.4+ or hstore for key-value pairs.

---

Of course, you can cast the integer to text, and work with a two-dimensional text array. Consider the two syntax variants for a array input in the demo below and consult the manual on array input.

There is a limitation to overcome. If you try to aggregate an ARRAY (build from key and value) into a two-dimensional array, the aggregate function array_agg() or the ARRAY constructor error out:

ERROR:  could not find array type for data type text[]

There are ways around it, though.

Aggregate key-value pairs into a 2-dimensional array

PostgreSQL 9.1 with standard_conforming_strings= on:

CREATE TEMP TABLE tbl(
 id     int
,txt    text
,txtarr text[]
);

The column txtarr is just there to demonstrate syntax variants in the INSERT command. The third row is spiked with meta-characters:

INSERT INTO tbl VALUES
 (1, 'foo', '{{1,foo1},{2,bar1},{3,baz1}}')
,(2, 'bar', ARRAY[['1','foo2'],['2','bar2'],['3','baz2']])
,(3, '}b",a{r''', '{{1,foo3},{2,bar3},{3,baz3}}'); -- txt has meta-characters

SELECT * FROM tbl;

Simple case: aggregate two integer (I use the same twice) into a two-dimensional int array:

Update: Better with custom aggregate function

With the polymorphic type anyarray it works for all base types:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Call:

SELECT array_agg_mult(ARRAY[ARRAY[id,id]]) AS x        -- for int
      ,array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS y -- or text
FROM   tbl;

Note the additional ARRAY[] layer to make it a multidimensional array.

Update for Postgres 9.5+

Postgres now ships a variant of array_agg() accepting array input and you can replace my custom function from above with this:

The manual:

array_agg(expression)
...
input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL)