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;

Could you show the entire method for when this update is happening, as well as the RLS policy on your `route` table? — dshukertjr, May 29 '23 at 06:21
I can't post all in comment. Made an UPD in question. Is it okay? — Scheffio, May 30 '23 at 13:50
Does the `route` table have only update RLS policy, or does it have other policies as well? Could you share all RLS policies applied to the `route` table? — dshukertjr, May 30 '23 at 23:44
Hmm, since anyone can sign in to your app, and any one can update or view any of the data, your RLS policy is not very secure, but that probably is not the issue here. Have you tested to run the query with RLS turned off? — dshukertjr, May 31 '23 at 03:20
Also, could you share the table definition of `route` just in case? — dshukertjr, May 31 '23 at 03:23

dshukertjr · Accepted Answer · 2023-06-02T00:32:10.863

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;

edited Jun 02 '23 at 00:32

answered May 31 '23 at 22:37

dshukertjr

15,244
11
57
94

Hm, made an another update with that function definition. What do you think? – Scheffio Jun 01 '23 at 12:26
@Scheffio Yup, that was precisely the cause. I have updated my answer. – dshukertjr Jun 01 '23 at 14:43
I guess it helped, but there is another one error `"column \"total_score\" does not exist"`. I additionally checked the code and `route` definition, and `total_score` exist. Have no idea... – Scheffio Jun 01 '23 at 14:58
I added `new.` to `total_score` and to `total_reviews`. Now the query is updating the table, but function isn't working. I mean, `new.rating = ROUND((new.total_score / new.total_reviews), 1);` doesn't do any calculations. Column `rating` is the same. – Scheffio Jun 01 '23 at 15:14
@Scheffio That seems like a separate question, so it would be nice if you could open another question. – dshukertjr Jun 02 '23 at 00:32
Sure! Made a new one - [Here it is!](https://stackoverflow.com/questions/76386634/supabase-function-doesnt-do-any-calculations) – Scheffio Jun 02 '23 at 01:35

Supabase won't take UPDATE query

1 Answers1