pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alban Hertroys <haramrae@gmail.com>
To: Dominique Devienne <ddevienne@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: WHERE col = ANY($1) extended to 2 or more columns?
Date: Fri, 10 Feb 2023 09:14:19 +0100
Message-ID: <8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com> (raw)
In-Reply-To: <CAFCRh-9ALd0kvGKxuj6oMMzxr2jcO=7nrcgx0V9vaGMZ0tbBhA@mail.gmail.com>
References: <CAFCRh-_7VMpCBFk6iGMN12gJF2hfX-iWU1QReVrjsgyJt0wKiQ@mail.gmail.com>
	<024441B3-5505-42FB-A665-8DE93B54A1C7@gmail.com>
	<CAKFQuwa20KW+Lza0NrOXTQxcpFJRQG=zjwz4LkB9u7DXxEHkBg@mail.gmail.com>
	<CAFCRh-9ALd0kvGKxuj6oMMzxr2jcO=7nrcgx0V9vaGMZ0tbBhA@mail.gmail.com>


> On 9 Feb 2023, at 18:35, Dominique Devienne <ddevienne@gmail.com> wrote:
> 
> On Thu, Feb 9, 2023 at 5:37 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys <haramrae@gmail.com> wrote:
> > On 9 Feb 2023, at 16:41, Dominique Devienne <ddevienne@gmail.com> wrote:
> > Now we'd like to do the same for composite keys, and I don't know how to do that.
> 
> This works:
> => 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.
> 
> 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…

=> with v(col1, col2) as (
	values (1, 'two'::text), (2, 'one'::text), (1, 'one'::text), (2, 'two'::text)
)
select * from v where (col1, col2) = (1, 'one'::text);
 col1 | col2 
------+------
    1 | one
(1 row)

This could be written as a join to a table with fixed values in the OP’s case.


Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.






view thread (11+ messages)  latest in thread

Message-ID: <8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com>
Permalink:  ../8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com/
Also on:    postgresql.org/message-id/8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: haramrae@gmail.com, ddevienne@gmail.com, david.g.johnston@gmail.com
  Subject: Re: WHERE col = ANY($1) extended to 2 or more columns?
  In-Reply-To: <8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com>
  References: <CAFCRh-_7VMpCBFk6iGMN12gJF2hfX-iWU1QReVrjsgyJt0wKiQ@mail.gmail.com> <024441B3-5505-42FB-A665-8DE93B54A1C7@gmail.com> <CAKFQuwa20KW+Lza0NrOXTQxcpFJRQG=zjwz4LkB9u7DXxEHkBg@mail.gmail.com> <CAFCRh-9ALd0kvGKxuj6oMMzxr2jcO=7nrcgx0V9vaGMZ0tbBhA@mail.gmail.com> <8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox