Re: WHERE col = ANY($1) extended to 2 or more columns?
От | Alban Hertroys |
---|---|
Тема | Re: WHERE col = ANY($1) extended to 2 or more columns? |
Дата | |
Msg-id | 8F053DBE-8B25-43BD-8822-78BD0B3E9FAC@gmail.com обсуждение исходный текст |
Ответ на | Re: WHERE col = ANY($1) extended to 2 or more columns? (Dominique Devienne <ddevienne@gmail.com>) |
Список | pgsql-general |
> 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 tobe 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 wouldneed 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.
В списке pgsql-general по дате отправления: