#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    7
    Rep Power
    0

    How to merge results of the arrays in PgSQL


    Assuming I have two fields in a table, like this:

    Column | Type |
    --------+-----------+
    id | integer |
    value | integer[] |


    SELECT * FROM t1;
    id | value
    ----- +-----------------------------------
    1 | {1, 2, 3}
    2 | {1, 4}
    3 | {1, 5}

    How would I make a query which returns a array, returned the merged the t1.values, i.e. something like:

    SELECT some_array_function(value) as newarray FROM t1;
    new_array
    --------------------------------
    {1, 2, 3, 4, 5}


    Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Code:
    select array_agg(distinct v) as newarray
    from (
      select unnest(value) v
      from t1
    ) n
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    7
    Rep Power
    0
    Originally Posted by shammat
    Code:
    select array_agg(distinct v) as newarray
    from (
      select unnest(value) v
      from t1
    ) n

    Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo