How to transfer a column in an array using PostgreSQL, when the columns data type is a composite type?

Question

I'm using PostgreSQL 9.4 and I'm currently trying to transfer a columns values in an array. For "normal" (not user defined) data types I get it to work.

To explain my problem in detail, I made up a minimal example. Let's assume we define a composite type "compo" and create a table "test_rel" and insert some values. Looks like this and works for me:

    CREATE TYPE compo AS(a int, b int);
    CREATE TABLE test_rel(t1 compo[],t2 int);
    INSERT INTO test_rel VALUES('{"(1,2)"}',3);
    INSERT INTO test_rel VALUES('{"(4,5)","(6,7)"}',3);

Next, we try to get an array with column t2's values. The following also works:

    SELECT array(SELECT t2 FROM test_rel WHERE t2='3');

Now, we try to do the same stuff with column t1 (the column with the composite type). My problem is now, that the following does'nt work:

    SELECT array(SELECT t1 FROM test_rel WHERE t2='3');
    ERROR:  could not find array type for data type compo[]

Could someone please give me a hint, why the same statement does'nt work with the composite type? I'm not only new to stackoverflow, but also to PostgreSQL and plpgsql. So, please tell me, when I'm doing something the wrong way.

Solution

There were some discussion about this in the PostgreSQL mailing list.

Long story short, both

select array(select array_type from ...)
select array_agg(array_type) from ...

represents a concept of array of arrays, which PostgreSQL doesn't support. PostgreSQL supports multidimensional arrays, but they have to be rectangular. F.ex. ARRAY[[0,1],[2,3]] is valid, but ARRAY[[0],[1,2]] is not.

There were some improvement with both the array constructor & the array_agg() function in 9.5.

Now, they explicitly states, that they will accumulate array arguments as a multidimensional array, but only if all of its parts have equal dimensions.

array() constructor: If the subquery's output column is of an array type, the result will be an array of the same type but one higher dimension; in this case all the subquery rows must yield arrays of identical dimensionality, else the result would not be rectangular.

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

For 9.4, you could wrap the array into a row: this way, you could create something, which is almost an array of arrays:

SELECT array(SELECT ROW(t1) FROM test_rel WHERE t2='3');
SELECT array_agg(ROW(t1)) FROM test_rel WHERE t2='3';

Or, you could use a recursive CTE (and an array concatenation) to workaround the problem, like:

with recursive inp(arr) as (
  values (array[0,1]), (array[1,2]), (array[2,3])
),
idx(arr, idx) as (
  select arr, row_number() over ()
  from   inp
),
agg(arr, idx) as (
    select array[[0, 0]] || arr, idx
    from   idx
    where  idx = 1
  union all
    select agg.arr || idx.arr, idx.idx
    from   agg
    join   idx on idx.idx = agg.idx + 1
)
select arr[array_lower(arr, 1) + 1 : array_upper(arr, 1)]
from agg
order by idx desc
limit 1;

But of course this solution is highly dependent of your data ('s dimensions).

Comments

[klin]

If you need to select an array of arrays of composite type this almost certainly means that your database model is ill-designed and requires complete reconstruction. You're in a dead end because of the earlier wrong decisions, don't waste your time.

[lorbeere]

You're definitively right. My database model is bad, because it's not even using the first normal form. But in my case this is (stupid as it sounds) correct, because my task is, to implement a Non-First-Normal-Form model (NF2) for university. I tried to make it as generic as possible and not bound to the overlaying application and now, at this point, got stuck.

[Erwin Brandstetter]

I guess you realize that the composite type is orthogonal to the problem?