`update().select().limit(1).single()` behavior not consistent
Hi we have two supabase projects. One's on postgres v15.1.1.73, the other is on v17.4.1.069.
on the v17, this works, on v15 it doesn't
to get it to work on v15, we had to remove the limit(1)
Is this a postgrest version issue?
27 Replies
error message for v15 is "Error fetching rows single or none for Table"
There should be no difference in the REST API between the two.
Are you using next.js?
Could it have been you changed RLS to fix something and by removing .limit() you busted its cache?
yep were using nextjs
i was able to reproduce it consistently even after the cache was busted
I just tested on my 15 instance and it works.
What version of PostgREST is your 15 on?
Mine is pretty old.
ah it only fails if it's an update then a select.
supabase.from('cat').select().limit(1).single() works fine
but supabase.from('cat').update({...updateObj}).select().limit(1).single() failsOK we are in the realm of I don't know if that is supported or not for update.
I apologize for not noticing update in your question. I so select limit single and went right to select.
That could also certainly be impacted by version of PostgREST used.
Can you list the two postgREST versions you are using?
EDIT I see at least one is 12.2.1
I see several changes in their logs over time....
PostgREST 13 says this??????
10 seems to have added it:
postgrest v12.2.1 for 15.1.73
it's breaking for the postgresql 15.1.73
Add the .order()
What version is it working for?
await supabase
.from('Table')
.update({
foo: ''
})
.eq('id', userID)
.select('*')
.limit(1)
.single();
this is working for postgres v17 with the latest postgrest 13.0.4
To me it looks like .order is required for all versions after 10.
The 13 comment is odd as it seems to say no it won't work any more.
but for postgres v15 wiht postgrest v12.2.1 :
this is the error message: "JSON object requested, multiple (or no) rows returned"
so the request isnt returning one row?
Yes because you have .single on it.
It is ignoring limit without an order, I believe.
It says it is required to have order. And you need order or your update could update any row in the table not just the last 1.
but there's a discriminant with the
.eq('id', userID) where id is the primaryKeyThen why are you limiting if there is only one row?
to ensure that there's only one row
and to have it return an object instead of an array
All I can say is it says you need .order() to use .limit on update.
or should i be removing the:
: single() and limit(1) and then treat the return data as an array?
ok ok
But at same time I don't understand the 13 change they made as it implies it won't work at all.
I guess it could be ignoring it and your data is such that it works?
You'll probably need to file an issue in supabase/postgres-js github. I'm sort of baffled at it.
If your .single() errors with an eq on a primary key then I assume you got no data back. (maybe because no .order). Then in 13 it just does not work at all so you just update the 1 one row. Also try just .single with no .limit().
gotcha ill test it out! and ill reach out to supabase/postgres-js github if the issue persists @garyaustin
thank you!