Re: WHERE col = ANY($1) extended to 2 or more columns?

From: Alban Hertroys <haramrae(at)gmail(dot)com>
To: Dominique Devienne <ddevienne(at)gmail(dot)com>
Cc: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: WHERE col = ANY($1) extended to 2 or more columns?
Date: 2023-02-10 08:14:19
Message-ID: 8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


> On 9 Feb 2023, at 18:35, Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
>
> On Thu, Feb 9, 2023 at 5:37 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> > On 9 Feb 2023, at 16:41, Dominique Devienne <ddevienne(at)gmail(dot)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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message GF 2023-02-10 10:20:51 Re: How to use the BRIN index properly?
Previous Message Bertrand Mamasam 2023-02-10 07:54:03 Re: pg_trgm vs. Solr ngram