Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cwKBv-0004oI-4b for pgsql-sql@arkaria.postgresql.org; Fri, 07 Apr 2017 03:04:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cwKBu-00053G-FE for pgsql-sql@arkaria.postgresql.org; Fri, 07 Apr 2017 03:04:02 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cwKAq-0001Ph-TF for pgsql-sql@postgresql.org; Fri, 07 Apr 2017 03:02:57 +0000 Received: from mail-oi0-x22f.google.com ([2607:f8b0:4003:c06::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cwKAo-0006Cn-K2 for pgsql-sql@postgresql.org; Fri, 07 Apr 2017 03:02:55 +0000 Received: by mail-oi0-x22f.google.com with SMTP id d2so72728871oig.1 for ; Thu, 06 Apr 2017 20:02:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=ckDW5t8wuX4H3E5p6o4z4eJshECFV6chG37BWmuXBIg=; b=YqVS409kQ7WdStHBvmXiUEj2qP+FTrhV0ZljtLwMZfq/ihgtTbOZmGlVUAQ7QRO2IC KGh/vlMojnK3GeU9AuOoPw3+9LLzd1oe4p+yyb6mHrlZNh5QhG4VThyg4ukld+gZ6DcD R9R0+Ic8PJAvcbTpPAINmJiPqZ5fQlQfW/wOFGPKPV2aNdjVkyuOf+iaBYuWPHT6lEEZ ZaflrQ7LPN9sQjkkjIqz28qr09sXmstkj04QkEaFbvb7x5nZ4I521WL0UdRdGWU4JwB1 xm7yWtopZzrfBZn8g4E+YBTjHK9bVKEgFCpZ3JNQ24X6AEFm8vVTS33d/quhPgbJYYLA h+5w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=ckDW5t8wuX4H3E5p6o4z4eJshECFV6chG37BWmuXBIg=; b=TckgQnzke1gLBEu7G2zEip5JJybSWr2o9erqdCQlJkPseEwwX4dkKQTUzOa9VP89mE SkIzZJyyQVxQnkr2FnVI5QrcgcleNlFvm/Q5APUOgqiWS6Tyt5diSiZmDATmjSWRTyNX iS9RPeOlA9y8wk+oBS2s0d/korj7NEcG/iRjVa6foah3mPKcouTlH2myT2GVjsNxJ2Xt 8tK4vpUcjqVJtqw78SSdVpl9G5+1QKQCmTrr/Jja9562ynHfZMlkZ95falSVWw1XPWRv VlVDL9vwGNa2XDL4N/lXL87HhhifUBMduQ73P5VeVuKPeCx8mSfxWYRknPTmc41rFDpL rU9w== X-Gm-Message-State: AFeK/H1/Lvo45zkqqUQaaHrXAMqIwHxWSCUldGerEb8h7K1844s9DRzKtRrQOAW7fEEKys5Gw4lgPN35iic/Ww== X-Received: by 10.157.52.69 with SMTP id v63mr11300305otb.57.1491534173885; Thu, 06 Apr 2017 20:02:53 -0700 (PDT) MIME-Version: 1.0 Received: by 10.157.10.202 with HTTP; Thu, 6 Apr 2017 20:02:53 -0700 (PDT) In-Reply-To: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com> References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com> From: "David G. Johnston" Date: Thu, 6 Apr 2017 20:02:53 -0700 Message-ID: Subject: Re: death of array? To: Rob Sargent Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=001a114152c69ad989054c8adc34 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a114152c69ad989054c8adc34 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Thu, Apr 6, 2017 at 7:32 PM, Rob Sargent wrote: > > I need to gather all segments whose probandset is within in a specified > people. > select s.* from segment s > join probandset ps on s.probandset_id =3D ps.id > --PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING: > =E2=80=8BSELECT s.* implies semi-joins - so lets see how that would work. SELECT vals.* FROM ( VALUES (2),(4) ) vals (v) WHERE EXISTS ( SELECT 1 FROM ( VALUES (ARRAY[1,2,3]::integer[]) ) eyes (i) WHERE v =3D ANY(i) ); // 2 =E2=80=8BHTH David J. --001a114152c69ad989054c8adc34 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Thu, Ap= r 6, 2017 at 7:32 PM, Rob Sargent <robjsargent@gmail.com> wrote:

I need to gather all segments whose probandset is within in a specified peo= ple.
select s.* from segment s
=C2=A0 join probandset ps on s.probandset_id =3D ps.id
--PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING:
=

=E2=80=8BSELECT s.* implies semi-joins - so lets see how = that would work.

SELECT vals.*=C2=A0
FROM ( = VALUES (2),(4) ) vals (v)
WHERE EXISTS (
=C2=A0 =C2=A0 SELE= CT 1 FROM ( VALUES (ARRAY[1,2,3]::integer[]) ) eyes (i)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 WHERE v =3D ANY(i)
);
// 2

=E2=80=8BHTH

David J.

<= /div>
--001a114152c69ad989054c8adc34--