Postgres 13

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 tbl (
  priority text,
  productive integer[],
  unproductive integer[],
  neutral integer[]
);

INSERT INTO tbl VALUES
  ('High'  ,'{1,2}','{}','{4,5}')
, ('Medium','{3,4}','{5,7}','{2}')
, ('Low'   ,'{1}','{2,6}','{}');
3 rows affected

TABLE tbl;


priority
productive
unproductive
neutral


High
{1,2}
{}
{4,5}

Medium
{3,4}
{5,7}
{2}

Low
{1}
{2,6}
{}


-- one row like original 
WITH cte AS (
   SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio
   FROM   tbl
   )
SELECT array_agg(id ORDER BY prio) FILTER (WHERE state = 'p') AS productive  -- ORDER BY prio?
     , array_agg(id ORDER BY prio) FILTER (WHERE state = 'u') AS unproductive
     , array_agg(id ORDER BY prio) FILTER (WHERE state = 'n') AS neutral
FROM  (
   SELECT DISTINCT ON (id) *
   FROM (
                SELECT unnest(neutral) AS id, prio, 'n' AS state FROM cte
      UNION ALL SELECT unnest(productive)   , prio, 'p'          FROM cte
      UNION ALL SELECT unnest(unproductive) , prio, 'u'          FROM cte
      ) sub1
   ORDER  BY id, prio, state
   ) sub2;


productive
unproductive
neutral


{1,2,3}
{7,6}
{4,5}


-- pivoted
WITH cte AS (
   SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio
   FROM   tbl
   )
SELECT state, array_agg(id ORDER BY prio)  -- ORDER BY prio?
FROM  (
   SELECT DISTINCT ON (id) *
   FROM (
                SELECT unnest(neutral) AS id, prio, 'neutral' AS state FROM cte
      UNION ALL SELECT unnest(productive)   , prio, 'productive'       FROM cte
      UNION ALL SELECT unnest(unproductive) , prio, 'unproductive'     FROM cte
      ) sub1
   ORDER  BY id, prio, state
   ) sub2
GROUP  BY 1;


state
array_agg


neutral
{4,5}

productive
{1,2,3}

unproductive
{7,6}