Supabase won't take UPDATE query

Question

I'm currently using supabase to make quick MVP for college work. I create feedback feature, so I have to update some columns in row. There is my JS query:

const { error: updateError } = await supabase.from('route').update({ total_score: 5 }).eq('id', 1);

Getting this error: screenshot of "preview" page error in dev tools Error code in dev tools: 400

If you don't want to look at screenshot: {"code":"21000",

"details":null,

"hint":null,

"message":"UPDATE requires a WHERE clause"}

And zero updates at supabase after this query. I have no idea what to do. I was using this query in my project some time ago, and it was fine. But here - no.

Any ideas how to fix this?

UPD: Function that calls and update query:

   async function setRating() {
    const rate = range.value.value
    const { error: updateError } = await supabase.from('route').update({ total_score: 5 }).eq('id', 1);

    console.log(updateError);
    // Commented this section cuz update query above doesn't work
    // if (!update_error) {
    //   console.log(update);
    //   route_feedback.value.classList.add('hide')
    //   route_ended.value.classList.add('show')
    //   setTimeout(() => {
    //     route_feedback.value.classList.remove('show')
    //     route_feedback.value.classList.remove('hide')
    //   }, 500);
    // }
  }
  }

RLS Policy on update in supabase:

---

Policy name: allow_update_on_route

Target roles: public

USING expression: true

WITH CHECK expression: true

---

Policy name: Authorized users can select from route table

Target roles: public

USING expression: (auth.role() = 'authenticated'::text)

---

UPD2: table route defenition:

create table
  public.route (
    id bigint generated by default as identity not null,
    name text null,
    country text null,
    city text null,
    description text null,
    imageName text null,
    created_at timestamp with time zone null default now(),
    total_reviews numeric null,
    total_score numeric null,
    duration numeric not null default '30'::numeric,
    rating double precision null,
    isAdult boolean not null default false,
    constraint route_pkey primary key (id)
  ) tablespace pg_default;

create trigger update_route_rating_trigger
after insert
or
update on route for each row
execute function update_route_rating ();

Without RLS still same error.

update_route_rating function defenition:

 BEGIN
    IF pg_trigger_depth() <> 1 THEN
        RETURN NEW;
    END IF;
   UPDATE route SET rating = ROUND((total_score / total_reviews), 1);
   return new;
END;

Answer 1

update_route_rating() function that you have set as the trigger is the cause here.

The following statement within your trigger function is attempting to update every row in your route table, because it does not have any where clause.

UPDATE route SET rating = ROUND((new.total_score / new.total_reviews), 1);

I'm assuming what you want to achieve here is to update the rating column for the newly inserted/updated row. In that case, you could use the following within your trigger function.

begin
    if pg_trigger_depth() <> 1 then
        return new;
    end if;
   new.rating = ROUND((total_score / total_reviews), 1);
   return new;
end;