Supabase #sql

ThePhilip

07/04/2022, 2:20 PM

works fine if I disable RLS

RegeX

07/04/2022, 2:28 PM

Hello ! I've a Supabase DB created with other developer, and someone of them (not in my team now) as created User Enum. But, now, I'm not unable to see or edit these UserEnum data. Someone who know how to update these data ? Thanks !

garyaustin

07/04/2022, 2:29 PM

RLS insert policy

silentworks

07/04/2022, 4:01 PM

Postgres types

Salvage_Dev

07/04/2022, 4:42 PM

Does the database on supabase support

Steve

07/04/2022, 7:00 PM

Thanks gary I guess rpc postgres would

dipankarmaikap

07/05/2022, 11:07 AM

Copy code

begin
  insert into public.profiles (id, email, name, location, confirmed_at)
  values (new.id, new.email, new.raw_user_meta_data->> 'name', new.raw_user_meta_data->> 'location', new.email_confirmed_at);
  return new;
end;

Hey I have this sql function when new user signs up it creates a profile for each. Its working but the confirmed_at field is not updating i dont know why, i tried

new.email_confirmed_at

and

new.confirmed_at

silentworks

07/05/2022, 1:29 PM

Email confirmed at

! Do you even Vim, bro?

07/05/2022, 10:01 PM

Any idea why this doesn't work? I just don't understand SQL well enough. Could anyone help me? I sort of laid this thing aside for a week or so and now I struggle. What I want is basically create a public table based on the values from a private one. I heard of

sets

and

queries

. Maybe they're the things I desperately need. I don't know! 🙏

Copy code

sql
create or replace function get_random_prices()
returns table (item_id int, value int)
as $$
declare
    random_prices table (item_id int, value int);
begin
    select id, floor(random() * (max_value - min_value + 1) + min_value)
    insert into random_prices  
    as value 
    from prices_ranges; -- private table, no policies set
    return random_prices;
end
$$ language plpgsql;

garyaustin

07/06/2022, 12:23 AM

returning table

chipilov

07/06/2022, 12:28 PM

Does anyone know if Supabase always uses the anon and authenticated users by default to execute requests (ignoring requests that involve SECURITY DEFINER stored procedures)? Are there any other users that need to be considered? The context is defining column-level permissions using GRANT/REVOKE statements

BigJ

07/07/2022, 8:11 AM

I'm trying to host supabase in an Azure spoke, and I've created a 'Azure Database for PostgreSQL'. This doesn't allow for superusers, I imagine this is a requirement for supabase? Or is it possible with just a pg_admin user?

BigJ

07/07/2022, 9:06 AM

The bypassrls role is also unassignable I see.

BigJ

07/07/2022, 9:08 AM

from what I can gather, this seems to only impact the service role?

bhaskar

07/09/2022, 2:18 AM

Hi, is there a way i could access

auth()

inside a view or function and check if

auth().role = 'authenticated'

? only authenticated users can read value from column A and if column B is "special value". if column b is "not special value" anyone can read it

garyaustin

07/09/2022, 2:34 AM

Sure. All the auth functions work for any sql involved in a postgREST handled request.

bhaskar

07/09/2022, 3:10 PM

works thank you!

jar

07/10/2022, 5:09 AM

How can I add a variable in this?

Copy code

select jsonb_path_query_array('[1,2,7,4,5]'::jsonb, ('$[0 to 2]'));

works

Copy code

select jsonb_path_query_array('[1,2,7,4,5]'::jsonb, ('$[0 to '||2||']'));

doesnt work

jar

07/10/2022, 2:49 PM

Oh wait i think like this is good

Copy code

select * from jsonb_array_elements('[1,2,7,4,5]'::jsonb) limit 3 offset 2;

jar

07/11/2022, 1:28 AM

I actually do still want first i think to keep it as jsonb list. Idk if these vars do anything useful

Copy code

select jsonb_path_query_array(
    '[6,7,1, 2, 3, 4]',
    '$[$min to $max]',
    '{"min": '||2||', "max": 3}'
);

weilzuvielgewalt

07/11/2022, 2:02 PM

Hey guys, I made a custom function to sort my shops by distance. There are also "premium shops" which should be listed first, no matter of the distance. Each studio has the column

isPremium

which is whether

true

false

. How can I do that? nearby_studios function (param:

location

)

Copy code

sql
select *
from studios
order by location <-> st_geogfromtext(nearby_studios.location);

I use it like this

Copy code

js
const { data: studios, error } = await client.rpc('nearby_studios',
    { location: `POINT(${userLocation.longitude} ${userLocation.latitude})` }
)
    .limit(50)
    .not('street', 'is', null)
    .not('city', 'is', null)

07/12/2022, 1:07 AM

I was wondering whether supabase support UNIQUE constrains on multiple columns on a table.

07/12/2022, 1:11 AM

Copy code

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

silentworks

07/12/2022, 1:26 AM

If Postgres supports it then Supabase supports it.

07/12/2022, 1:28 AM

I checking my database and it doesn't seem my table columns got the UNIQUE constrains.

07/12/2022, 1:29 AM

I only see the UNIQUE constrains work if it only for one column for the table

07/12/2022, 1:31 AM

Copy code

//This works
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a)
);

// This did not work
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

silentworks

07/12/2022, 1:38 AM

It might not show in the UI but its there in the database, maybe this is a bug. Can you open a issue on GitHub https://github.com/supabase/supabase/issues about this please?

07/12/2022, 1:47 AM

Your right. It is a UI problem

Fehmi

07/12/2022, 10:43 AM

Hi, I am trying to create a spatial table by using

ogr2ogr

from my laptop (basically, I am trying to import a MapInfo TAB into the DB which PostGIS activated) but getting the error below

ERROR 1: ERROR:  permission denied for table spatial_ref_sys

I think it is because the

spatial_ref_sys

table is owned by

supabase_admin

user and my default db user does not have write access to that table. How can I fix this? Thanks