Increment Not Working

tsnakejake · 2022-10-05T06:21:13.120Z

Hello there! I am having a problem doing a basic task in supabase. I am trying to increment a number in a column but it doesn't seem to be working. I am very new to backend and a total beginner to SQL, so I may be doing something silly 😅

T

tsnakejakeOP•10/5/22, 6:21 AM

And here is my code in JS:

const { data, error } = await supabase
        .rpc('increment', { discord_id: '143100912687251456' })

const { data, error } = await supabase
        .rpc('increment', { discord_id: '143100912687251456' })

const { data, error } = await supabase
        .rpc('increment', { discord_id: '143100912687251456' })

const { data, error } = await supabase
        .rpc('increment', { discord_id: '143100912687251456' })

T

tsnakejakeOP•10/5/22, 6:22 AM

T

tsnakejakeOP•10/5/22, 6:22 AM

data

data

and

error

error

both log

null

null

G

garyaustin•10/5/22, 12:24 PM

Do you have RLS on the table? You will need to meet it for update.

Ggaryaustin Do you have RLS on the table? You will need to meet it for update.

T

tsnakejakeOP•10/5/22, 7:10 PM

I have RLS enabled, but with 0 active policies. I am using my supabase secret key to initialize my client, so it should be able to bypass RLS restrictions

G

garyaustin•10/5/22, 7:11 PM

Do you also have a signed in user, or just the supabase client initialization and then your call?

Ggaryaustin Do you also have a signed in user, or just the supabase client initialization an...

T

tsnakejakeOP•10/5/22, 7:12 PM

Just the supabase client initialization and then my call

T

tsnakejakeOP•10/5/22, 7:12 PM

This is a Node.js backend

T

tsnakejakeOP•10/5/22, 7:20 PM

I think it may be my increment function not working, because when I update directly like this...

 const result = await supabase.from("rankings").update({score: 5}).eq("user", "143100912687251456")

const result = await supabase.from("rankings").update({score: 5}).eq("user", "143100912687251456")

 const result = await supabase.from("rankings").update({score: 5}).eq("user", "143100912687251456")

const result = await supabase.from("rankings").update({score: 5}).eq("user", "143100912687251456")

It works perfectly fine

G

garyaustin•10/5/22, 7:24 PM

Yeah sounds like it, I just glanced at as RLS is the usual culprit.

T

tsnakejakeOP•10/5/22, 7:25 PM

Oh you were totally right, I needed to add RLS and do the supabase service key for any update to work

T

tsnakejakeOP•10/5/22, 7:25 PM

It's just now I can't get the

rpc

rpc

rpc

rpc call to work with

increment

increment

increment

increment, strange...

T

tsnakejakeOP•10/5/22, 7:28 PM

OMGGGGGGGGGGGG I figured it out

T

tsnakejakeOP•10/5/22, 7:29 PM

Turns out

user

user

user

user is a built in keyword, and can't be used as a column name

G

garyaustin•10/5/22, 7:30 PM

I just warned someone about that the other day.... It is so easy to miss.... I found it in theirs because they posted their code and I loaded in my Datagrip sql editor which flags that as an issue. You can put it in double quotes but that is a bad idea.

T

tsnakejakeOP•10/5/22, 7:34 PM

Thank you for your help @garyaustin !

T

tsnakejakeOP•10/5/22, 7:44 PM

By the way @garyaustin , is there a way to set the

score

score

score

score to 1 if it doesnt exist yet in the same increment procedure?

G

garyaustin•10/5/22, 7:48 PM

update rankings set score = coalesce(score, 0) + 1 WHERE not_user = discord_id

update rankings set score = coalesce(score, 0) + 1 WHERE not_user = discord_id

update rankings set score = coalesce(score, 0) + 1 WHERE not_user = discord_id

update rankings set score = coalesce(score, 0) + 1 WHERE not_user = discord_id

T

tsnakejakeOP•10/5/22, 7:50 PM

So the way to think of this in english is:

We're updating rankings table
We get all rows where
```
not_user = discord_id
```
not_user = discord_id
```
not_user = discord_id
```
not_user = discord_id
We set those filtered scores equal to 1 higher, or 1

T

tsnakejakeOP•10/5/22, 7:50 PM

This is my first experience with SQL, it's weird to me how the logic is actually in the wrong order

T

tsnakejakeOP•10/5/22, 7:50 PM

Meaning the logic is to update rankings -> filter -> set
But it's written as update rankings -> set -> filter

G

garyaustin•10/5/22, 7:51 PM

coalesce is actually looking for a value in score and if null then uses the 2nd value

T

tsnakejakeOP•10/5/22, 7:51 PM

Oh yes, I was meaning more how

update

update

update

update,

set

set

, and

where

where

are written

T

tsnakejakeOP•10/5/22, 7:52 PM

I haven't dealt with backend/SQL coding before but logically I would think you do

update

update

update

update

where

where

and then

set

set

T

tsnakejakeOP•10/5/22, 7:52 PM

But it's written in a different order

G

garyaustin•10/5/22, 7:52 PM

LOL you dare question a language that has been around 50 years.

T

tsnakejakeOP•10/5/22, 7:52 PM

Hahahahahahha

T

tsnakejakeOP•10/5/22, 7:53 PM

But why though

T

tsnakejakeOP•10/5/22, 7:53 PM

I have too much Javascript in my head

T

tsnakejakeOP•10/5/22, 7:54 PM

Also I don't think that is working, it won't insert a new row

G

garyaustin•10/5/22, 7:55 PM

Oh sorry thought it was just the column that was null

T

tsnakejakeOP•10/5/22, 7:55 PM

Don't be sorry! No, the whole row doesn't exist in this case

G

garyaustin•10/5/22, 7:58 PM

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/ you asked for it...

PostgreSQL Tutorialadmin

PostgreSQL Upsert Using INSERT ON CONFLICT statement

This tutorial shows you how to use the PostgreSQL upsert feature to insert or update data if the row that is being inserted already exists in the table.

G

garyaustin•10/5/22, 8:01 PM

Basically you do the insert and then if it sees the id exists (conflict) you tell it to do the update instead.

T

tsnakejakeOP•10/5/22, 8:04 PM

T

tsnakejakeOP•10/5/22, 8:04 PM

Getting some syntax error... debugging now

G

garyaustin•10/5/22, 8:06 PM

Drop the table name after update, it knows if from the insert part and this is all one statement.

T

tsnakejakeOP•10/5/22, 8:07 PM

T

tsnakejakeOP•10/5/22, 8:07 PM

One step closer

T

tsnakejakeOP•10/5/22, 8:07 PM

How are you debugging this by the way? Is there a SQL editor

G

garyaustin•10/5/22, 8:08 PM

That one I just looked at. If you post your code in triple backticks then others can more easily run a check on it in a full editor. I have Datagrip I use.

T

tsnakejakeOP•10/5/22, 8:09 PM

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = coalesce(score, 0) + 1
    WHERE discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = coalesce(score, 0) + 1
    WHERE discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = coalesce(score, 0) + 1
    WHERE discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = coalesce(score, 0) + 1
    WHERE discord_id = _discord_id
$$
language sql volatile;

T

tsnakejakeOP•10/5/22, 8:09 PM

Ahh like this

T

tsnakejakeOP•10/5/22, 8:09 PM

Will check out Datagrip

G

garyaustin•10/5/22, 8:15 PM

There are other free ones out there but I've not used them as that is a free extension for my IDE.

T

tsnakejakeOP•10/5/22, 8:17 PM

I got it to work!!!

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = rankings.score + 1
    WHERE rankings.discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = rankings.score + 1
    WHERE rankings.discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = rankings.score + 1
    WHERE rankings.discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = rankings.score + 1
    WHERE rankings.discord_id = _discord_id
$$
language sql volatile;

T

tsnakejakeOP•10/5/22, 8:18 PM

I don't really understand why I have to put

rankings.discord_id

rankings.discord_id

rankings.discord_id

rankings.discord_id but not

SET rankings.score

SET rankings.score

SET rankings.score

SET rankings.score on the previous line

T

tsnakejakeOP•10/5/22, 8:18 PM

How does it know the

score

score

score

score column exists but not

discord_id

discord_id

discord_id

discord_id

G

garyaustin•10/5/22, 8:19 PM

I don't understand that one... I'm not seeing it flagged as an error in my editor, but I also don't have your table, so it is flagging table name and all the columns anyway.

T

tsnakejakeOP•10/5/22, 8:19 PM

Yeah that makes sense. Anyways, thank you for your help. I will investigate further

Increment Not Working

Similar Threads

Similar Threads

Similar Threads