From: | "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com> |
---|---|
To: | Hannes Erven <hannes(at)erven(dot)at> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: array_agg and/or =ANY doesn't appear to be functioning as I expect |
Date: | 2018-01-22 02:21:39 |
Message-ID: | CACg0vT=DaTsJJm6SthN_Ys152OCzeVZyoaqkn8y3H3Obf2oNdg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hannes,
Thanks for your observations...... Will take a look at the data.
Regards,
Rhys
On Jan 20, 2018 11:00 PM, "Hannes Erven" <hannes(at)erven(dot)at> wrote:
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thiemo Kellner | 2018-01-22 06:39:53 | Setting up streaming replication problems |
Previous Message | Michael Paquier | 2018-01-22 01:07:44 | Re: Best non-networked front end for postgresql |