Re: array_agg and/or =ANY doesn't appear to be functioning as I expect - Mailing list pgsql-general

Hi Rhys,

Am 2018-01-21 um 02:42 schrieb Rhys A.D. Stewart:

Greetings All,
I'm having an issue which is very perplexing. The having clause in a
query doesn't appear to be working as I expect it. Either that or my
understanding of array_agg() is flawed.

>

> [...]

with listing as (
       select start_vid, end_vid, array_agg(node order by path_seq)
node_array, array_agg(edge order by path_seq) edge_array
     from confounded.dataset
       group by start_vid,end_vid
     having true =ALL (array_agg(truth))
  )
select count(*) from confounded.dataset
where node in (select distinct unnest(node_array) from listing) and
truth = false;

I would expect the above query to return 0 rows.

the answer is in your data: "node" is not a UNIQUE field, and there are node values with multiple rows.
e.g. node=977 has one row with truth=true and one with truth=false.

So what your second query really does is "select all node values from listing for which another entry with truth=false exists in the dataset".

Presuming that "seq" is a primary key [although not declared], you probably meant to restrict your query on that.


Best regards,

        -hannes