Select into a 2D array

Question

In PostgreSQL, how can I select some values into a 2D array?

For example, if I want to aggregate a field of data into an array, I would use the ARRAY() constructor or array_agg function e.g.

ARRAY(SELECT s.name FROM myschema.mytable s)

How would I go about selecting say, two values, into a 2D array i.e.

2DARRAYFUNCTION(SELECT s.name, s.id FROM ...)

Yep :) The problem is really coming from the fact that my postgreSQL to C# data reading library (NPGSQL) really has no way of easily intreperetting the data I'm trying to pull back (nested types or rows of the 'record' pseudotype). Hence trying key-value pairs, array of arrays, 2D arrays etc. — harman_kardon, Feb 07 '12 at 13:35
Obviously a follow up to this one: stackoverflow.com/q/9129855/939860 — Erwin Brandstetter, Feb 07 '12 at 18:00

score 4 · Accepted Answer · edited May 23 '17 at 12:22

4

If you try to aggregate an ARRAY (build from key and value like in your example) into a two-dimensional array, the aggregate function array_agg() or the ARRAY constructor error out:

I happen to have written an answer for this case exactly recently. Turns out, it's your prequel question.

edited May 23 '17 at 12:22

Community

1
1

answered Feb 07 '12 at 12:19

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Feb 07 '12 at 11:28

0

Check documentation HERE.

answered Feb 07 '12 at 11:28

aF.

64,980
43
135
198

Select into a 2D array

2 Answers2