great!

Hello everyone! I'm trying to write a function that receives as an argument an array of "ids", then I want to check for every id provided if: - A row in a table exists with that id - And if exists, that some column = true And finally if all provided "ids" passes this validation, insert a row for each id in a table. The use case is for an chat app where the user can subscribe to multiple rooms at once if the room exists and its public. Does anyone has an idea now can I handle it? I'm trying different approaches without much success yet thanks in advance!

Let's say I have some private table (no policies at all). I want to write a database function that uses some of its data and returns a new one. How could it be done? Thanks in advance!

Hello guys, I was planning to implement a table structure similar to the following one in my project (see picture below). But looking at it, I was thinking that inheritance could be a better fit, isn’t it? My media table would have a Many-to-one relation to multiple other tables. So I was thinking that I could just have a base « Media » table and one sub Media table per other table. That would increase performance since I could just select, insert, delete in a specific small table instead of a big Media table? I would like to have advices on this. Thanks in advance!

You use a security definer function to ignore the RLS policy (or lack there of assuming RLS is enabled). You can do any security you need with where or if statements in that function. Then you call that function with the API.

any idea why this works for me?

SELECT * FROM "recipe-vessels" WHERE "recipeId" = 9 AND  "token" IN ('iD95euC3k5Yujjke5RiCnmxryvSjwLfVXRn7vgUF5kb','GWNyy4QANs7ABsFWi9EmxE35xqyFmd8WumW3Sw1zMZsu','7j8GTVndHzUChCnQ38fEgioJVs6bV3nEg7GtDrbbTyy9','2bLNqfUNAKEKjWSARBrSWzURGDMnZSDfSPBMshjnBvX3','AtBqAd2FLjZx4n7Fc6XGKuaqKixVZtYZT6yVKGYPPgyW','CGjvdsMqg9awSN5bawjPvZxTce1dZo2qyq2h59foXG6h','5dkCnu3hv4ML48as2ws5jnsa1G8EU6GSMsm1bGLM7zdM','3jEJnSgsRVQDHRTBJNhM8wzXud9bQhJKCNcTneCRaK7X','43PHQvXGfntx9xUodNbKCihMwLgZ73pFqAx1zxSC6VZg','CBA6N5SobR6Ci2bp52Txhp9AhXP8Dt1NSqj9UFLwgRaZ','8BpWmdch8oymicXRuZPu4DqWUYPJxBrQzk9RCFi7T2cs','3QWDsinp9zyFg1mC2RmF9NvvTTpzjLCqUfvYT3sVSbYi','CUpuoS8ZWXE3iqP5mG5LqrNufzScUog25UyNyfSY2crh','4z2kvKU8JYebFqXXcSqGSNLypydoYLrobYuBQMuDzFnw')

but this doesn't?

export async function getVessels(tokens:string[], recipeId:number) {
  try {
    const user = supabase.auth.user()

    let { data, error, status } = await supabase
      .from('recipe-vessels')
      .select(`*`)
      .eq('recipeId',recipeId)
      .in('token',tokens)

    if (error && status !== 406) {
      throw error
    }

    return data
  } catch (error) {
    throw(error)
  }
}

nevermind, it was a RLS policy issue 😅

but making the question made me realize it! haha

RPC insert into a table with uuid

invalid input syntax for type uuid 'text'

return new id of inserted row of 2 tables

Hey guys, I struggle to understand why my query is slow :

sql
begin;
explain (analyze,buffers,timing)
SELECT "public"."Typebot"."id",
  "public"."Typebot"."workspaceId"
FROM "public"."Typebot"
WHERE ("public"."Typebot"."id") NOT IN (
    SELECT "t0"."id"
    FROM "public"."Typebot" AS "t0"
      INNER JOIN "public"."Result" AS "j0" ON ("j0"."typebotId") = ("t0"."id")
    WHERE (
        (
          NOT (
            "j0"."createdAt" >= '2022-05-31 22:00:00 UTC'
            AND "j0"."createdAt" <= '2022-06-29 22:00:00 UTC'
          )
        )
        AND "t0"."id" IS NOT NULL
      )
  )
ORDER BY "public"."Typebot"."id" ASC
LIMIT 100 OFFSET 0;
rollback;

Here is the output: https://gist.github.com/baptisteArno/19f3939a428ec0ee25a64611345a6002 I'm not sure how to interpret this? 😦

Hey @baptisteArno , I recommend taking a look at this youtube video on the topic:

Hello, just started to use supabase today and I am not quite understanding how it works when you are joining tables with or without foreign keys. I have the data model as in the image and I want to get all galaxies a user is subscribed to. So the SQL I want to do is: select name from galaxies inner join subscriptions on galaxies.id = subscriptions.galaxy_id where subscriptions.user_id = '74c5deb6-ac02-4b3c-bad4-d7b775fde8af' my attempt at doing this in flutter looked like final res = await _client .from("galaxies") .select("name, subscriptions(user_id)") .eq("subscriptions.user_id", uid) .execute(); But then I get all names of excising galaxies with the user_id if it matches. If someone could point me in the right direction to translate with SQL to flutter query I would be greatful. 🙂

Trying to run

psql -h (db url) -p 5432 -d postgres -U postgres

based off the template and getting

psql: error: connection to server at "db url" (3.88.4.237), port 5432 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?

Same thing with pg admin

Trying to run `psql h db url p 5432 d

Hey guys! When i try to create a new table from inside the editor on my self hosted version, the tables have only the user postgres as owner. Is that the expected behavior? Its missing users like anon, authenticated, postgres, service_role...

ALTER TABLE IF EXISTS public.stripe_products
    OWNER to postgres;

Does the database on supabase support creating multiple databases with CREATE DATABASE?

Hey guys I currently to try model a permission system between my 3 Entities (Department, Team and User) and would love to hear some feedback or ideas on the best way to model the database.

I drew a quick note so it is easy to understand

Right now I got a table schema of a

n:n

between a

User

and

Team

(so one user can be in multiple teams and one team has multiple users) and I attached a

Role

field to that

n:n

table to store if it is a user or a trainer

Now I struggle to bring the

Department

entity in the mix without making the whole schema too confusing

I would like to avoid double role checks in my backend to lookup 2 different tables to see if a user has the permission to edit something in a team

If a team can only be in one dept, then have a foreign key in you team table pointing to a dept. then to access the team you check if users team array is the team row id , or his dept array is the foreign key to dept. in the row. So only need team table for both and then the user info in a table and/or part of the users meta data that comes in with the jwt.

This works. Is the recommended approach? Or maybe you destructure x,y in the as part idk

select regr_slope((val->>'val')::numeric,(val->>'open_time')::numeric) from jsonb_array_elements('[
  { "open_time": 161, "val": 15 },
  { "open_time": 162, "val": 9 },
  { "open_time": 163, "val": 12 },
  { "open_time": 164, "val": 7 },
  { "open_time": 165, "val": 6 },
  { "open_time": 166, "val": 9 },
  { "open_time": 167, "val": 8 }
]') as val;

Hey guys When i try to create a new

Why is my connection via psql timing out today? It wasnt timing out last week

Tried it with psql and with DBeaver and both time out. When I use my webapp using the REST API it works. Tried restarting DB server

Is there something wrong with my RLS, getting error on insert call