2

How to select array_agg(ARRAY[f1_date,ARRAY[f2_int,f3_decimal]])? There is an error about combining date and integer in ARRAY.

upd: added picture explaining where and how I plan to use array. The issue is db size. After transforming 3 colunms to multidimensional array I can save plenty of space. It will be 4M rows instead of 200M. Each row will have array with maximum 500 elements inside.

enter image description here

revoua
  • 2,044
  • 1
  • 21
  • 28

2 Answers2

3

Arrays in Postgres share the same base element across all dimensions.

Array of anonymous records

You can build an array of anonymous records (as base type):

SELECT array_agg((i,d))
FROM  (
   VALUES
     (1::int, 2.3::decimal)
    ,(2, 3.4)
    ,(3, 4.5)
   ) x(i, d);

This is rather unwieldy though, as you cannot access subfields of anonymous records by name (names do not exist!). May be more practical to operate with well know types ..

Composite type as base type

Create a composite type and use it as base type for your array. 

CREATE TYPE int_dec AS (i int, d decimal);

SELECT '(1, 2.3)'::int_dec AS id_base
      ,'{"(1, 2.3)","(2, 3.4)","(3, 4.5)"}'::int_dec[] AS id_arr

-- Build an array from composite base type
SELECT array_agg(a)
FROM (
    VALUES
      ('(1, 2.3)'::int_dec)
     ,('(2, 3.4)'::int_dec)
     ,('(3, 4.5)'::int_dec)
    ) x(a);

-- Build an array from composite base type
SELECT array_agg((i,d)) AS anonymous_arr
      ,array_agg((i,d)::int_dec) AS id_arr
FROM  (
   VALUES
     (1::int, 2.3::decimal)
    ,(2, 3.4)
    ,(3, 4.5)
   ) x(i, d);

Table as base type

Any table can serve as composite type.

db=# CREATE TEMP TABLE int_dec (i int, d decimal);
CREATE TABLE
db=# INSERT INTO int_dec VALUES (1, 2.3), (2, 3.4), (3, 4.5);
INSERT 0 3
db=# SELECT array_agg(id) FROM int_dec id;
            array_agg
---------------------------------
 {"(1,2.3)","(2,3.4)","(3,4.5)"}

Text as common ground

The alternative is to cast all values to text since every data type can be cast to text and back in PostgreSQL and build a multi-dimensional array.

For that you may be interested in aggregating multi-dimensional arrays. Consider the answer under this related question:
Selecting data into a Postgres array

In my experience there is often a better solution than to build complex arrays, though.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • as I need multi-dimensional array the only way is to cast all elements to text, right? A added some explanation - maybe that will clarify my issue better. – revoua Jan 30 '13 at 02:05
  • 1
    @revoua: It's *one* way, but not the only one. You could build an array on a composite type consisting of a `date` and the composite `int_dec` outlined in my answer. If you don't know exactly what you are doing I would advice to keep tables instead. An RDBMS is at its best with *tables* in many respects. – Erwin Brandstetter Jan 30 '13 at 16:15
0

I believe that PostgreSQL does not support arrays that combine multiple types. It should be either all dates, or all integers, but not both mixed together.

Now, if you really want to combine both, you could convert dates to integers, but using Unix time. For example, 1359478323 would represent ISO 8601:2013-01-29 16:52:03Z. You'll find bi-directional conversion libraries in pretty much any programming language.

Ismael Ghalimi
  • 3,515
  • 2
  • 22
  • 25