CODEWITHSUNDEEP
supabasesupabase-database
stuart
stuart

Reputation: 2283

How to bulk delete in Supabase using multiple columns?

is the supabase api able to delete multiple rows at a time which are each identified by multiple column values?

something like:

const { data, error } = await supabase
  .from('list_members')
  .delete()
  .in('{list_id,contact_id}', ['{7,2}', '{7,4}']);

?

as opposed to a bulk delete using a single column which is supported:

const { data, error } = await supabase
  .from('cities')
  .delete()
  .in('id', ['1', '2'])

(aka this question but for supabase)

UPDATE

if anyone's curious this is the postgres function i wrote instead, based on much SO guidance. no guarantee on efficiency but it works.

create or replace function bulk_delete_list_members (list_ids bigint [], contact_ids bigint [])
  returns setof bigint
  language PLPGSQL
  as $$
begin
    return query WITH deleted AS (

      delete from list_members
      where (list_id, contact_id) in (
        select list_id, contact_id
        from unnest(list_ids, contact_ids) as x(list_id, contact_id)
      ) returning *

    ) SELECT count(*) FROM deleted;
end;
$$;

Upvotes: 1

Views: 2729

Answers (1)

Monica
Monica

Reputation: 244

I don't think this is possible, but another way to do this would be to write a PostgreSQL function and call it using .rpc().

Check out this discussion thread on GitHub: github.com/supabase/supabase/discussions/3419

Upvotes: -1

Related Questions