Depending on what you are doing you would use

auth.uid() = ANY (array_col)

Would anyone be willing to help me with some complex SQL? Or tell me I shouldn't be doing this in the DB... I'm working on a hobby project to book a product, the product has a schedule with one or more (non overlapping) opening times, say from 9 to 12 and from 13 to 21. There can be 5 appointments at the same time (5 rooms). Then we have a booking table that contains all the actual bookings. I want to be able to return available timeslots based on the user telling me how long they want to book. I made an Excel to visualize a calendar of sorts. Attached is a snippet of that (can share the google sheets also in case interested). The user would tell us: which product, on which date, for how long they want to book.

Would anyone be willing to help me with

Does anyone know if I can put some plpgsql inside of a pg_cron command ? I keep having syntax errors :/

just call a function.

didn't think of that but yeah sounds obvious, thanks ._.

Anyone have any tips for how I can get access to the

user_metadata

object on an auth user? I can see the data is saved/returned back with the auth user if I pass in custom data to an auth call, but I am trying to add some of the meta data to my create user function that triggers with a new auth user is created. So far I havent been able to get it to work and I think its due to the way Im trying to access the property ->

new.user_metadata.source

create function public.init_profile_from_auth_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
declare
  merchant_id uuid;
  shop_id uuid;
begin
  merchant_id := extensions.uuid_generate_v4();
  shop_id := extensions.uuid_generate_v4();

  insert into public.merchants(id)
  values(merchant_id);

  insert into public.shops(id, merchant_id)
  values(shop_id, merchant_id);

  insert into public.users (id, email, shop_id, source)
  values (new.id, new.email, shop_id, new.user_metadata.source);

  return new;
end;
$$;

If you look in the dashboard table ui (change schema to auth) you can see the user table and the column is raw_user_meta_data and jsonb. So to access it new.raw_user_meta_data or for a specific item new.raw_user_meta_data->>name.

Thank you! Also we thanks for pointing out where to look to see the column types for the user table 💪🏼

i am getting below error for sql - Failed to run sql query: column "branchcode" does not exist screenshot of my schema and sql editor query. looks like a noob mistake. thanks in advance.

Caps https://discord.com/channels/839993398554656828/885237287280070708/988097402059780098

Hi all -- I have tables

members

,

teams

and

sites

-- and I would like to be able to select

member_id

,

team_id

and an array of that team's `site_id`s. I'm still learning SQL -- is there a resource someone can point me to in order to return `site_id`s as an array in one row instead of separate rows with duplicate member and team data? Thanks!

i have 2 postgress queries

(select branchcode, sum(valuein) as my_sum from db where datetime between {{ moment(dateSelector.value).format('YYYY-MM-DD') }} and {{ moment(dateSelector.value).format('YYYY-MM-DD') }} group by branchcode ORDER BY "branchcode" ASC)

and

(select branchcode, sum(valuein) as my_sum_weekly from db where datetime between {{ moment(dateSelector.value).startOf('week').format('YYYY-MM-DD') }} and {{moment(dateSelector.value).format("YYYY-MM-DD")}}group by branchcode ORDER BY "branchcode" ASC)

how do i combine the results vertically

branchcode | my_sum | branchcode | my_sum_weekly

any ideas?

Hey all I am trying to setup an RLS where it is using joins. There are 2 tables that i am referencing. Is this possible or is this something that I should handle on API routes manually. The two tables are

user_profiles

and

agencies

the

user_profiles

have a shared key of

agency_id

which is a

uuid

I have looked a little bit in the Postgres docs and haven't been able to come up with much. Is there a good way to convert the below statement into an RLS ?

select email, user_id, ag.agency_id

from auth.users u
join user_profiles up on up.user_id = u.id
left join agencies ag on ag.agency_id = up.agency_id

RLS can use joins. It is just an added where statement. The thing to watch out for (especially for select RLS) is you can't reference the table you are putting the RLS on in a select as then you have a recursive condition.

RLS can use joins It is just an added

Hi all. Does anyone knows how to return only the value from a relationship with PostgREST? Consider the following:

javascript
supabase
  .from("items")
  .select(`
    name,
    type:types (
      name
    ),
    price:types (
      price
    )
  `)
  .csv()

If possible, I want to get the values from the relationships (

type

and

price

from the table

types

using the database without having to process this again using JS so that I can get the CSV directly from supabase DB. I'm not sure what terms to look for. Still getting used to Postgres and PostgREST.

Can anyone recommend a good video course for Postgres on Udemy or somewhere else that is affordable?

Hey, How can I add an Item to an array on supabase without deleting the other items on the array? like in the push method kinda way

I also responded in your other thread, please only post in one place. This is the better place to have asked than in chit chat. The answer is no, you have to use rpc with a postgres function and array operation.

Can you help me write it, I don't understand anything from that language.

You are going to be hard pressed to use an SQL database without writing any SQL. This link https://ubiq.co/database-blog/how-to-update-array-in-postgresql/ I googled shows the code for various array operations. You would enter that in the dashboard database function UI, with the language declared as SQL. Then call it with an rpc call. https://supabase.com/docs/reference/javascript/rpc Lots of help should be available with a bit of searching especially using Postgres (the underlying SB database) in the search.

I've been stuck for hours creating a policy. I have condensed it down to the simplest text query, but when attached to a table, no rows are returned when querying.

sql
('training.view'::text IN (
  SELECT test_permission.app_permission FROM test_permission) 
)

Data in test_permission:

Like it just doesn't make sense. Since the rows in test_permission DO contain 'training.view'. And when attaching this policy to any table whatsoever, I imagine it to ALWAYS return true.

OMFG. IT WAS BECAUSE I HAD RLS ENABLED ON THE TABLE I WAS QUERYING.

Has anyone used the ADDRESS_STANDARDIZER extension? Im completely lost on how this works! 😤

I beat my head against a wall trying to figure this out, but the source column on my users table accepts an enum `('WEB', 'IOS', 'ANDROID')`; For some reason grabbing a value from a the raw_user_meta_data that returns one of the accepted enum values wasnt working. If I changed the source column to just text, it would save. Seems like a nuance with taking a jsonb value and having it recognized as an enum?

Hi, I want to create a row level security that allows only the creator to select/insert/... his records. I created the rls's for the first two tables. I could apply the following example on my second table as they are connected through a foreign key:

create policy "Team members can update team details if they belong to the team."
  on teams
  for update using (
    auth.uid() in (
      select user_id from members
      where team_id = id
    )
  );

Now I want to create a rls for the third table which is also connected via a foreign key to the second table. table1 table2 table3 How can I apply the example above to join the second table first and then the first where I can access the user_id?

Hey, I have a table with column of id and another one with info, how can I delete All the data in the info column and to keep the id column - so now i have id :1 info: null id:2 info : null

etc