Supabase•3y ago

How can I prevent creating a new record?

Is it possible to prevent inserting a new record if it has some data matching another record in the table? Currently I have to do a filter query on my table to see if there is an existing record with the same follow_by and twit_id of the record I want to add

  let { data, error } = await supabase
    .from('new-follows')
    .select('username,follow_by,twit_id')
    .eq('follow_by', String(follow_by))
    .eq('twit_id', String(twit_id));

  let { data, error } = await supabase
    .from('new-follows')
    .select('username,follow_by,twit_id')
    .eq('follow_by', String(follow_by))
    .eq('twit_id', String(twit_id));

I run that on each record I want to add and if they return nothing then I proceed with inserting new records:

  let { data, error } = await supabase
    .from('new-follows')
    .insert(followsTostore);

  if (error) {
    throw new Error(error.message);
  }
  return data;

  let { data, error } = await supabase
    .from('new-follows')
    .insert(followsTostore);

  if (error) {
    throw new Error(error.message);
  }
  return data;

Doing that first query for each record I potentially want to add really adds up though! 😦 Is there another way? Can I create some sort of table policy or edge function so I don't have to the first query for each new record?

5 Replies

garyaustin•3y ago

Looks like you want the pair of columns unique, if so you can and a unique constraint on the pair. https://stackoverflow.com/questions/14221775/in-postgresql-force-unique-on-combination-of-two-columns

Stack Overflow

In Postgresql, force unique on combination of two columns

I would like to set up a table in PostgreSQL such that two columns together must be unique. There can be multiple values of either value, so long as there are not two that share both. For instanc...

garyaustin•3y ago

You can also use that approach to insert or update with upsert based on the constraint of the two columns matching.

dunstonOP•3y ago

this sounds great! though i just started using postgresql a week ago and I have no idea how this in Supabase

garyaustin•3y ago

The dashboard only allows unique columns. You will do this in postgres in the sql window if you are using the dashboard. This is not a Supabase specific feature, so you just add it by altering the table in sql. You then would have to deal with insert errors though if they violate the constraint. But you don't have to do a precheck select.

dunstonOP•3y ago

ty sir

Gaming

Programming

How can I prevent creating a new record?

Did you find this page helpful?