Hi, I need some help in updating an jsonb[], this is how my schema looks like, { "id":"8a015caf-aa3b-4044-9f88-9f64fbcdbf8e", "question":"Test question 2", "options":[ { "id":"cf38e648-fbd8-4b20-9fb7-bdd0476d71e5", "text":"option1", "votes":0}, { "id":"c23ef382-3a27-4a15-bca0-ab156bdaad24", "text":"option2", "votes":0 } ], "total_votes":0 }

How would like to find an option by id and increment the votes of that option by 1 ?

This is how I did it using mongoose/mongodb

ts
const poll = await Poll.updateOne(
          { "options.id": req.query.id },
          {
            $inc: { "options.$.votes": 1 },
          }
        );

hi guys .. im new in supabase also in development ^^ but i was wondering how can i pull data through an api from e.g airtable and cashing it in supabase and then used these data from supabase api

is that could happen ?

pull data through an api

update jsonb

Hello, can you cancel before an update event with a trigger function ?

Cancel before an update with trigger function

Hi All, I'm trying to modify the database in my project using DBeaver, I can connect no problem and see everything, but when I try to make any changes to the DB I get 'ERROR: must be owner of table xxxxxx', I used the credentials in the UI but it seems like that might using a different user to create tables? Any help is appreciated

table owner

Hey everyone, question on returning messages in a specific order. I have a messaging component in my application and I'm trying to return the latest 5 messages to the user in a reverse order (newest message last / at the bottom, like a messaging app). Any advice how to do this? I have the limit set, so I'm getting the 5 latest, but when I try to sort, if I do it desc, then I get the 5 oldest messages. Any ideas?

@User Did you find a solution to your problem? Otherwise, sorting by desc should get you the the five newest messages but reversed for your purpose, so the newest message would be the first row that is returned. You could use this as a subquery and reverse the returned rows again, like so:

select * from (select * from messages order by sent_at desc limit 5) m order by sent_at asc

.

that sounds about right, will give it a try. I was thinking there should be some order of operations sort desc (to get the latest first), limit 5, then sort asc or something similar

Is there an example of authentication/access to auth table using direct connection to the database?

you can see the auth table by running

SELECT * FROM auth.users

if you have the necessary permissions, for example via the Supabase UI SQL editor

I mean how do I do authenticated requests not via supabase js orm but via direct connection to postgres

Authenticated requests in Supabase are handled by an intermediate server called PostgREST - supabase.js's requests are actually sent to PostgREST, PostgREST then inspects the token send by the request and then sends a request to the db by setting the necessary configuration options on the connection/session so that auth.uid(), auth.email() etc correspond to the user. If you don't want to use supabase.js and PostgREST, this means you would need to implement this flow all by yourself

@chipilov 👍

hi, its there a way to do aggregate functions using supabase? like sum and avg

With the Postgres functions you can do any of the postgres aggregate functions and then use an rpc function to access those if needed. https://www.postgresql.org/docs/14/functions-aggregate.html

the rls rules are applied to the rpc functions?

also i tried using a view, but the rls rules aren't applied there

Hi, how are we supposed to make more complex queries? I want to insert a row into Table B and then update a row in Table A with the id of that new row. Currently, I'm just doing a insert and then a separate update but I want to combine it into a single query. Thanks

RLS rules on rpc

Anybody know how I can set a column data type to be an incremental number that starts at a certain number i.e 10000?

You could use a Serial value and then set the sequence to start at 10000

Once you setup the table you can use

select setval('column_serial_seq', 10000);

Depending on what the sequence gets called

this gives some examples http://sqlines.com/postgresql/datatypes/serial