Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: death of array?
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: <223726fa-1013-6fde-b45b-70f5ed6e655f@gmail.com>
Date: Sat, 8 Apr 2017 15:07:43 -0600
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <CED7D0FC-59B7-4F15-9F72-A879907BC210@gmail.com>
References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com>
 <CAKFQuwZeaKm7xo-bFDSzMbB2=yxHvxeFXAc2vSSmYoVCeuHyZQ@mail.gmail.com>
 <58E7319B.9090306@matrix.gatewaynet.com>
 <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com>
 <CAKFQuwbiU4viib21wWKa3T_4w343MioOjeqxkeeRYkMD5hqsfA@mail.gmail.com>
 <d1ce18b8-c5cb-9547-c0b3-8a4910f96483@gmail.com>
 <CAKFQuwYZeUjUz1rBOJSCeO08zg1ugzOLVpPA8BuZVe5pvK1cnw@mail.gmail.com>
 <223726fa-1013-6fde-b45b-70f5ed6e655f@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


> On Apr 7, 2017, at 10:17 AM, Rob Sargent <robjsargent@gmail.com> wrote:
>=20
>=20
> On 04/07/2017 10:02 AM, David G. Johnston wrote:
>> On Fri, Apr 7, 2017 at 8:57 AM, Rob Sargent <robjsargent@gmail.com> wrot=
e:
>> Understood but  true if any match is found, I need every array member to=
 match as I want them all to be of a specific people as input into the quer=
y (so field =3D all(array_col))  and "field" here would be people_member.pe=
rson_id and using that yields zero results in full query (very quickly thou=
gh).
>>=20
>> =E2=80=8BI didn't actually attempt to comprehend your original email.  I=
f you want to supply a self-contained, functioning, query and expected outp=
ut (ideally something simpler but that covers your main question) I'd be in=
clined to dig further.  Even a broken one with what you think should work w=
ould be
>>=20
>> person =3D ALL(persons) doesn't really seem like it will typically work.
>>=20
>> Here are the various array operators supplied by PostgreSQL.  You might =
find one of them helpful.  In particular "contains".
>>=20
>> https://www.postgresql.org/docs/9.6/static/functions-array.html
>>=20
>> David J.
>>=20
>> =E2=80=8B
>=20
> Well you prodding got me a seriously reduced execution time.  Here's the =
actual query. The tables are all in the 'seg' schema and not substantively =
different than described in first post.
>=20
> create temp table opt10aut
> as=20
> with optset as (
>     select s.id
>            , s.markerset_id
>            , s.startbase
>            , s.endbase
>            , ((s.events_equal + s.events_greater)/(1.0 * (s.events_less +=
 s.events_equal + s.events_greater))) as pval
>     from seg.segment s
>          join seg.probandset i on s.probandset_id =3D i.id
>      join (select people_id, array_agg(person_id) as persons from seg.peo=
ple_member group by people_id) as pa on i.probands <@ pa.persons
>      join seg.people o on pa.people_id =3D o.id=20
>    where
>          s.markerset_id =3D '61801888-9a81-4187-922c-4d42c0471bea'
>          and o.name =3D '709'
> )
> select m.name
>        , min(pval)
> from optset op
>      join seg.markerset_member mm on op.markerset_id =3D mm.markerset_id
>      join seg.marker m on mm.member_id =3D m.id
> where
>      m.basepos between op.startbase and op.endbase
> group by m.name
>=20
> It is true that this particular people has only 60 members.  Our upper en=
d would be ~1000.
>=20
> rjs
>=20
>=20
>=20

What I was hoping for, I guess, is a construct like
    create table t(id int /*for Achilles*/, idset int[] references v.field)=
=20
and it would be understood that each element can be found in (preferably th=
e index of) v.field
Oh, and an operator to make use of the knowledge something like
    select * from t join v on t.idset =E2=80=9Care_all_in=E2=80=9D v.field;

Thanks to all,
rjs


=09

--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql