Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQOY8-00051S-Uc for pgsql-general@arkaria.postgresql.org; Fri, 10 Feb 2023 08:14:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQOY6-0002PF-TL for pgsql-general@arkaria.postgresql.org; Fri, 10 Feb 2023 08:14:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQOY6-0002P6-GN for pgsql-general@lists.postgresql.org; Fri, 10 Feb 2023 08:14:26 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pQOY4-0000YM-5G for pgsql-general@lists.postgresql.org; Fri, 10 Feb 2023 08:14:26 +0000 Received: by mail-ej1-x62d.google.com with SMTP id sa10so13644124ejc.9 for ; Fri, 10 Feb 2023 00:14:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=ifn5WTETkTVEXDL7UVBmSrxZGaEOu2T+o4xLUOWg4p0=; b=iDldOooIc6McJxT5reVeQbhuWyOLTxh+Bf6y5DNsOnZRyUaxjObZPT1lGwaEYmUqF+ 80opw1lertOWigynpOxHlqiWE3OCtEicart60LvA0VnrARqqXtr5jmop++crbg7rB+B6 G3vVCJcQftpax+UuY+zAVU8+vB/VARSOf1wta+T+nF0Yi5RtmdpNlQNnUcyBZA20wdBJ y4Iy3mT6XcVJXJ7JCltlCt9cqWoCX03cHAPHfkYvLFUS8erPVd3/TYZ1IPnBH26cA7kY awYVtLA0edU/BYhpL9N1kND/Xdlk0o4Ng6KD/u7J7bBhmmMtuu1fBIbbTDafUT069NUv +jBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=ifn5WTETkTVEXDL7UVBmSrxZGaEOu2T+o4xLUOWg4p0=; b=1yTs4nx8jd0cWeVYpB+AIVIuP/ViPbswxIIZNnO08JPRWA+YO4Nx9nnD/7VI8WrHPS HU/cqL7vjjAgoi7WtEKNmIJr55rfCYicQjH3BsgRTek7+J6Ll7MsEehgN/joOc6Ma+5j mZGC6N8r8rqz1YuX7EQx7tALhYxHRsPTdwhjBhEiB+ojtkCajsZEfmnpebzZiW6zNgSf 7g6rzr2boB6xF8EemtAY1HcxYTYa6No32NoR0RLGlu4m84XfR9WdqwPrWSvQKSMVffw7 ff40oj59GJwbAVPcL0aUbHPb1gcTdPeKZ0g7yiDCerzy1fOmLZwYU/pL8p3IPeOM52PT cTBQ== X-Gm-Message-State: AO0yUKV6/QNrP1PVZ5K9XZlBR14mrd/bcLvr+s3lJ7FofyqP6wA/twPw tckl5+CutuXhzFIyKcQVpHY= X-Google-Smtp-Source: AK7set/XTbV1o1XufpnJTWCkXufM6AdT0BH9y3GI02lwvH8ZOtl6QE4zeAZfWGlaz1LNLxeHXhLvHw== X-Received: by 2002:a17:907:8a03:b0:8aa:bdeb:83b5 with SMTP id sc3-20020a1709078a0300b008aabdeb83b5mr15956078ejc.18.1676016861903; Fri, 10 Feb 2023 00:14:21 -0800 (PST) Received: from hollewijn.internal (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id s8-20020a170906168800b00887a23bab85sm2031332ejd.220.2023.02.10.00.14.20 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 10 Feb 2023 00:14:21 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: WHERE col = ANY($1) extended to 2 or more columns? From: Alban Hertroys In-Reply-To: Date: Fri, 10 Feb 2023 09:14:19 +0100 Cc: "David G. Johnston" , pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com> References: <024441B3-5505-42FB-A665-8DE93B54A1C7@gmail.com> To: Dominique Devienne X-Mailer: Apple Mail (2.3608.120.23.2.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 9 Feb 2023, at 18:35, Dominique Devienne = wrote: >=20 > On Thu, Feb 9, 2023 at 5:37 PM David G. Johnston = wrote: > On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys = wrote: > > On 9 Feb 2023, at 16:41, Dominique Devienne = wrote: > > Now we'd like to do the same for composite keys, and I don't know = how to do that. >=20 > This works: > =3D> select (1, 'one'::text) in ((1, 'two'::text), (2, 'one'::text), = (1, 'one'::text), (2, 'two'::text)); > But you cannot write the right-side of the IN as a single parameter = which seems to be the primary constraint trying to be conformed to. >=20 > Right. The goal is to (re)use a prepared statement (i.e. plan once), = and bind the RHS (binary) array > and do a single exec (single round-trip) to get the matching rows. = AFAIK, this is the fastest way. > If there's a better/faster way, I'm interested. --DD How would an ORM like that push a list of tuples into a single query = parameter though? Is that feasible? Perhaps this is easier to use with an ORM then? It would need a list of = placeholders for each item, but I suspect you would need that anyway=E2=80= =A6 =3D> with v(col1, col2) as ( values (1, 'two'::text), (2, 'one'::text), (1, 'one'::text), (2, = 'two'::text) ) select * from v where (col1, col2) =3D (1, 'one'::text); col1 | col2=20 ------+------ 1 | one (1 row) This could be written as a join to a table with fixed values in the = OP=E2=80=99s case. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.