DELETE for more complex conditions, especially composite primary keys?

[fjf2002]

Environment

• PostgreSQL version: 13.4
• PostgREST version: 8
• Operating system: WSL 2 on Windows 10

Description of issue

Question: Can DELETE use more complex conditions, especially with tables that have composite primary keys?

Let's assume mytable has primary key (id, name). I admit something like

DELETE https://postgrest/mytable?(id,name)=eq.(14, 'foo')

... is currently not possible but a feasible workaround is

DELETE https://postgrest/mytable?id=eq.14&name=eq.foo

But what about

DELETE https://postgrest/mytable?(id,name)=not.in.((14, 'foo'), (15, 'bar'))

i. e. one wants to delete all rows except those with primary keys (14, 'foo') and (15, 'bar').

As far as I know this is currently not possible with postgrest(?)

I suspect it all boils down to having kind of a "PostgreSQL ROW Constructor" equivalent in postgrest?

[fjf2002]

Ah I figured out I can emulate

DELETE https://postgrest/mytable?(id,name)=not.in.((14, 'foo'), (15, 'bar'))

by using

DELETE https://postgrest/mytable?or=(id.neq.14,name.neq.foo)&or=(id.neq.15,name.neq.bar)

[wolfgangwalther]

DELETE https://postgrest/mytable?or=(id.neq.14,name.neq.foo)&or=(id.neq.15,name.neq.bar)

Does that really work? I'm not sure whether we support multiple filters with the same name (or= x2). I would expect that the second one overrides the first and that this is essentially just a or=(id.neq.15,name.neq.bar). Could also be the first one, but you get the point.

I*d write that as and=(or(id.neq.14,name.neq,foo),or(id.neq.15,name.neq.bar)).

[fjf2002]

Using the following table (which is simply the cartesian product of [14, 15, 16] with [foo, bar, baz]):

14	foo
14	bar
14	baz
15	foo
15	bar
15	baz
16	foo
16	bar
16	baz

GET https://postgrest/mytable?or=(id.neq.14,name.neq.foo)&or=(id.neq.15,name.neq.bar)

... returns all rows BUT (14,foo) and (15,bar) as I would expect when multiple "or" parameters work, and

DELETE https://postgrest/mytable?or=(id.neq.14,name.neq.foo)&or=(id.neq.15,name.neq.bar)

... deletes exactly those rows, leaving only (14,foo) and (15,bar).

So I can conclude multiple "or" parameters seem to work, right?

That's more readable having "&" infix instead of "and=" prefix.