3

Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column,

SELECT
  unnest(array_agg(id)) AS id
FROM foo
GROUP BY CUBE (a,b,c,d)
HAVING count(*) > 1 

If I try to move the unnest to FROM there, I get

ERROR:  aggregate functions are not allowed in functions in FROM
LINE X: CROSS JOIN LATERAL unnest(array_agg(id)) AS id

You can create the test data to run against this, like this

CREATE TABLE foo AS
SELECT row_number() OVER (), *
FROM ( VALUES
  ( 'X', 'D', 'G', 'P' ),
  ( 'F', 'D', 'L', 'M' ),
  ( 'X', 'N', 'R', 'S' ),
  ( 'Y', 'I', 'W', 'Q' ),
  ( 'U', 'Z', 'E', 'H' )
) AS f(a,b,c,d);

What's a better way to write the above?

Is there anything less convoluted than,

SELECT k FROM (
  SELECT array_agg(id) AS x
  FROM foo
  GROUP BY CUBE (a,b,c,d)
  HAVING count(*) > 1
) AS t
  CROSS JOIN LATERAL unnest(x) AS k;
  • 2
    Nope, I think your solution is the standard way to express it. It definitely is convoluted. – Craig Ringer Dec 9 '16 at 4:11
  • Could you please explain what is the purpose of this code? – David דודו Markovitz Dec 9 '16 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.