By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE results (n text, a int[], q int);
INSERT INTO results VALUES
('foo', '{1,2}'::int[], 1)
, ('foo', '{3,4,5}', 1)
;
CREATE TABLE
INSERT 0 2
-- create custom aggregate func for simple concatenation ONCE
CREATE AGGREGATE array_concat (anycompatiblearray) (
sfunc = array_cat
, stype = anycompatiblearray
, initcond = '{}'
);
SELECT n, array_concat(a) AS a, sum(q) AS q
FROM results
GROUP BY n;
CREATE AGGREGATE
| n | a | q |
|---|---|---|
| foo | {1,2,3,4,5} | 2 |
SELECT 1
-- Oliver's workaround -- for exactly 2 dimensions in the intermediarry aggregate
SELECT n, ARRAY(SELECT JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(JSONB_AGG(a)))) AS a, SUM(q) AS q
FROM results
GROUP BY n;
| n | a | q |
|---|---|---|
| foo | {1,2,3,4,5} | 2 |
SELECT 1
-- workaround with jsonb -- for up to 2 dimensions in the intermediarry aggregate
SELECT n
, ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*]')::int) AS a
, sum(q) AS q
FROM results
GROUP BY n;
| n | a | q |
|---|---|---|
| foo | {1,2,3,4,5} | 2 |
SELECT 1
-- NOTE the effect on multi-dimensional arrays!
INSERT INTO results VALUES
('bar', '{{6,7},{8,9}}', 2) -- 2-dim
, ('bar', '{{10,11},{12,13}}', 3) -- 2-dim
;
SELECT n, array_concat(a) AS a, sum(q) AS q
FROM results
GROUP BY n
ORDER BY n;
INSERT 0 2
| n | a | q |
|---|---|---|
| bar | {{6,7},{8,9},{10,11},{12,13}} | 5 |
| foo | {1,2,3,4,5} | 2 |
SELECT 2
-- Add not-matching array dimesions
INSERT INTO results VALUES
('baz', '{{66,67},{68,69}}', 4) -- 2-dim
, ('baz', '{0}' , 5) -- 1-dim
;
INSERT 0 2
-- custom aggregate FAILS now!
SELECT n, array_concat(a) AS a, sum(q) AS q
FROM results
GROUP BY n
ORDER BY n;
ERROR: cannot concatenate incompatible arrays DETAIL: Arrays with differing dimensions are not compatible for concatenation.
-- Oliver's workaround FAILS for > 2 dims!
SELECT n, ARRAY(SELECT JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(JSONB_AGG(a)))) AS a, SUM(q) AS q
FROM results
GROUP BY n;
| n | a | q |
|---|---|---|
| bar | {"[6, 7]","[8, 9]","[10, 11]","[12, 13]"} | 5 |
| foo | {1,2,3,4,5} | 2 |
| baz | {"[66, 67]","[68, 69]",0} | 9 |
SELECT 3
-- my jsonb workaround FAILS for > 2 dims!
SELECT n
, ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*]')::int) AS a
, sum(q) AS q
FROM results
GROUP BY n;
ERROR: cannot cast jsonb array to type integer
-- extend Oliver's jsonb workaround to 3 dims
SELECT n, ARRAY(SELECT jsonb_array_elements(jsonb_array_elements(jsonb_array_elements(jsonb_agg(a))))) AS a, SUM(q) AS q
FROM (
VALUES
('bar', '{{6,7},{8,9}}'::int[], 2) -- 2-dim
, ('bar', '{{10,11},{12,13}}' , 3) -- 2-dim
) results (n,a,q)
GROUP BY n;
| n | a | q |
|---|---|---|
| bar | {6,7,8,9,10,11,12,13} | 5 |
SELECT 1
-- but that fails for anything else:
SELECT n, ARRAY(SELECT jsonb_array_elements(jsonb_array_elements(jsonb_array_elements(jsonb_agg(a))))) AS a, SUM(q) AS q
FROM results
GROUP BY n;
ERROR: cannot extract elements from a scalar
-- extend my jsonb workaround to 1-3 dims
SELECT n
, ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*][*]')::int) AS a
, sum(q) AS q
FROM results
GROUP BY n
ORDER BY n;
| n | a | q |
|---|---|---|
| bar | {6,7,8,9,10,11,12,13} | 5 |
| baz | {66,67,68,69,0} | 9 |
| foo | {1,2,3,4,5} | 2 |
SELECT 3
-- workaround with plain SQL
SELECT n, a, q
FROM (
SELECT n, sum(q) AS q
FROM results
GROUP BY n
) q
FULL JOIN (
SELECT n, array_agg(elem) AS a
FROM results, unnest(a) AS elem
GROUP BY n
) a USING (n)
ORDER BY n;
| n | a | q |
|---|---|---|
| bar | {6,7,8,9,10,11,12,13} | 5 |
| baz | {66,67,68,69,0} | 9 |
| foo | {1,2,3,4,5} | 2 |
SELECT 3