Array of Arrays in Postgresql

Array of arrays in PostgreSQL

From a curiosity standpoint, does anyone know why these are not supported?

One generic answer is because arrays are intrinsically anti-relational. Removing repeating values is how you achieve 1st normal form. To have repeating groups of repeating groups seems quite insane from a relational theoretical standpoint.

In general, the relationally-correct thing to do is to extract a table for your repeating values. So if you modeled something like this:

CREATE TABLE users (
  id integer primary key,
  name varchar,
  favorite_colors varchar[],
  ...
);

it would behoove you to redefine this relationally like so:

CREATE TABLE users (
  id integer primary key,
  name varchar,
  ...
);

CREATE TABLE favorite_colors (
  user_id integer references users,
  color varchar
);

Or even:

CREATE TABLE users (
  id integer primary key,
  name varchar,
  ...
);

CREATE TABLE colors (
  color varchar primary key
);

CREATE TABLE favorite_colors (
  user_id integer references users,
  color varchar references colors,
  primary key (user_id, color)
);

Hstore supports a lot of functions, many of which would make it easy to integrate it into a relational worldview. I think the simplest way to solve your problem would be to use the each function to convert your hstore values into relations you can then use like a normal set of values. This is how you address having multiple values in other databases anyway: querying, and working with result sets.

postgres array of arrays to columns

unnest() for a regular multidimensional array has the problem, that it expands all elements of all dimensions, not only the first one. So, there is no way for these types to get this done.

A possible workaround is casting the array into a JSON array. For JSON arrays this is not a problem:

demo:db<>fiddle

SELECT
    id,
    elems ->> 0 AS col1,
    elems ->> 1 AS col2,
    elems ->> 2 AS col3
FROM
    table_a,
    json_array_elements(array_to_json(arr)) elems

json_array_elements() expands the first dimension of the array, so the subarrays are moved into their one row. Afterwards simple get their elemens by their indexes.

---

The query above eliminates the NULL rows because of its comma notation (which, in fact, is the shortcut for INNER JOIN LATERAL in this case). This can be avoided using an LEFT OUTER JOIN LATERAL instead:

demo:db<>fiddle

SELECT
    id,
    elems ->> 0 AS col1,
    elems ->> 1 AS col2,
    elems ->> 2 AS col3
FROM
    table_a
LEFT JOIN LATERAL
    json_array_elements(array_to_json(arr)) elems ON TRUE

Unnesting an array of arrays, to a simple array in plpgsql

You can't cast the result of a set returning function to an array.

DO
$do$
DECLARE
  nested   varchar[][] := '{{a},{b},{c}}';
  unnested varchar[];
BEGIN    
  unnested := array(select * from unnest(nested));
END
$do$;

Postgresql array of arrays is equal?

Order your arrays in ARRAY_AGG, then you can use = to compare them to your ordered array literal:

SELECT
  things.id,
  ARRAY_AGG(DISTINCT ARRAY[parts.id, coalesce(parts.color_id, things.default_color)
FROM things
JOIN parts_has_things ON parts_has_things.thing_id = things.id
JOIN parts ON parts.id = parts_has_things.part_id
HAVING
ARRAY_AGG(DISTINCT ARRAY[parts.id, coalesce(parts.color_id, things.default_color)] ORDER BY parts.color_id) = ARRAY[ARRAY[1,1],ARRAY[2,1],ARRAY[3,2]]::BIGINT[][]

PostgreSQL: Without using a function, how can nested arrays be compared?

Since there is no array_except function or operator in PostgreSQL, you have to unpack the array in your query:

SELECT id,
       CASE missing_categories
            WHEN ARRAY[NULL]::text[]
            THEN ARRAY[]::text[]
            ELSE missing_categories
       END
FROM (SELECT tab.id,
             array_agg(arr.elem) AS missing_categories
      FROM tab
         LEFT JOIN LATERAL unnest(tab.categories_a) AS arr(elem)
            ON NOT tab.categories_b @> arr.elem
      GROUP BY tab.id) AS q;

The outer query only replaces an array with a NULL element that occurs because of the outer join with an empty array.

POSTGRESQL: Group the values as ARRAY of ARRAYS

Use ARRAY_AGG, and perform a two-level aggregation. First, aggregation by system and quarter to generate a series of records containing arrays for each quarter. Then, aggregate a second time by system alone to generate a single array-of-arrays.

WITH cte AS (
    SELECT
        System,
        ARRAY_AGG(Value1 ORDER BY NameYear) AS Val1,
        ARRAY_AGG(Value2 ORDER BY NameYear) AS Val2
    FROM yourTable
    GROUP BY System, Quarter
)

SELECT
    System,
    ARRAY_AGG(Val1) AS Val1,
    ARRAY_AGG(Val2) AS Val2
FROM cte
GROUP BY
    System;

Demo

postgresql Aggregate arrays into a single array with union of all elements

Something like this should work:

with mytable as
(
select 1 as id, array[1, 2, 3, 4] as myarray

union all

select 1 as id, array[5, 6] as myarray

union all 

select 1 as id, array[7, 8] as myarray
)

select
  id,
  array_agg(elements order by elements)
from mytable, unnest(myarray) as elements
group by id

There's some discussion about building the custom function here: Concatenate/merge array values during grouping/aggregation

Convert string to array of arrays in PostgreSQL

You can simply replace the brackets with braces, and you have the string representation of the array, which you can cast to the desired data type.

SELECT CAST (replace(
                replace(
                   '[[1.1, 1], [2.2, 2]]', '[', '{'
                ), ']', '}'
             ) AS float[]
            );

      replace      
-------------------
 {{1.1,1},{2.2,2}}
(1 row)

Abelisto's version is even simpler:

SELECT translate('[[1.1, 1], [2.2, 2]]', '[]', '{}')::float[];

Here, :: is the PostgreSQL shortcut for the standard CAST syntax.

---

Related Topics