Hey folks, another SQL question. I am wanting to check if a single string value exists within an array:

'admin' = any(select roles from public.users_orgs where user_id = auth.uid() and org_id = orgs.id limit 1)

and I'm having all sorts of trouble.

roles

is a

text[]

Hello people, how can I make a conditional filter in an rpc, similar to the example image.

You could use

WHERE id IS NULL OR tb1.id = id

Hey everyone, I'm trying to build an RLS policy that checks if a text[] contains a particular value. The text[] is returned from a function and that function grabs it's value from a table:

create or replace function public.user_orgs() returns uuid[] as $$
  select array_agg(org_id) from users_orgs where user_id = auth.uid() limit 1;
$$ language sql stable;

And the RLS policy has a

WITH CHECK

that I'm trying to make work like this:

"admin" in (select roles from users_orgs where user_id = auth.uid() and org_id = orgs.id)

If I use admin in

"

quotes it interprets it as a column name and if I use

'

quotes it has a malformed array error.

Hi, i am trying to merge responses from 2 queries and display it columwise. query 1

SELECT branchcode, valuein from daily WHERE CAST(datetime AS DATE) = {{date1.value}} ORDER BY "branchcode" ASC

query2

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

Desired output column-wise branchcode, valuein, my_sum i tried using WITH, and subquery - but ran out of luck. thanks in advance.

I've wrote few psql functions and for some reason sql queries were disappeared from sql editors. How to find source code of those functions in supabase?

I ve wrote few psql functions and for

Hello there! I am trying to make a filter to get only entries that have the column 'liquidity' >= 1000 OR the column 'contracts' equals to [] (it's a table of arrays) Here is where I am supabase .from('assets') .select('*') .or('liquidity.gt.1000,contracts.eq.[]') I am trying to find the correct expression for contracts.eq.[] - thanks a lot!

Hey folks I have a trigger function

CREATE or REPLACE function auth.update_token() RETURNS TRIGGER AS $$
  DECLARE
    alias text;
  BEGIN
    SELECT "public"."orgs".alias into alias from "public"."orgs" WHERE id = new.org_id;
    UPDATE users SET raw_app_meta_data = jsonb_set(raw_app_meta_data, ARRAY['roles', NEW.org_id::text], to_jsonb(NEW.roles)) WHERE id = NEW.user_id;
    IF trim(alias) <> '' THEN
      UPDATE users SET raw_app_meta_data = jsonb_set(raw_app_meta_data, ARRAY['roles', alias], to_jsonb(NEW.roles)) WHERE id = NEW.user_id;
    END IF;
    RETURN NEW;
  END;
$$ language plpgsql;

And a trigger:

CREATE TRIGGER "Update token with allowed orgs" AFTER INSERT OR UPDATE
ON "public"."users_orgs"
FOR EACH ROW EXECUTE PROCEDURE auth.update_token();

But the trigger doesn't seem to be running the updates 🤷‍♂️

Update not running

Any idea why I am not able to to use "st_asgeojson()" function. Trying to play with sql editor (never used postgis before). I have enabled the extension aswell and all other which mention the "postgis".

Hello, I'm kinda new to postgres SQL and I'm wondering how can I simplify this full text search function. What I'd like to do is calculate expression under where and order by clause ( setweight(to_tsvector... ) only once and then supply it as a variable because it's repeating 2 times. Any suggestions?

sql
create or replace function search_items(lang regconfig, query text)
  returns table (title text, description text)
  language plpgsql
  as
  $$
declare
  query_vector tsquery = to_tsquery(lang, query);
begin
  return query
    select 
      item.title, 
      item.description
    from item
    where (setweight(to_tsvector(lang, item.title), 'A') || ' ' || 
           setweight(to_tsvector(lang, item.description), 'B')) @@ query_vector
    order by ts_rank((setweight(to_tsvector(lang, item.title), 'A') || ' ' || 
                      setweight(to_tsvector(lang, item.description), 'B')), query_vector) 
    desc;
end;
$$;

combine SQL queries

Up, please help me on that 😦

Try .eq.{}.

In postgresql, what data type should I use to store VAT rates used when calculating taxes, such as VAT rate = 24% = 0.24 It would be easy to store it as a smallint and give it the value 24, but 24 is not the same thing as 24%

I mean its not that complicated to handle that in your front end. Just choose one approach and deal with it in your front end framework. If you store it as 0.24, you just have to multiply it the total bill or whatever to get the tax. If you store it as 24, you have to divide it with 100 and then multiply with the total bill. You just have to make sure that you stick with it through out the app.

Well I was thinking of how I should store and handle it inside postgres. Someone else told me to use numeric(4, 4) as type. And I could do checks using scale(rate) and rate >= 0 😀 I think that feels like a good solution. Then all values will have to be between 0 and 0.9999 (0-99,99%)

Postgres format for %

I have a table with some int2[] type fields. How would I write this query to substitute an empty array for each of the appropriate fields (I know the name of the fields with arrays)

const { data: profileHazards, error } = await db
        .from('profile')
        .select(
            'site_hazards,other_site_hazards,land_adjacent_hazard,other_hazards'
        )
        .eq('id', _session.user.id);

Hello I can't add a column to database, it seems like there is not enough disk space, the table size is about 2GB, so 4GB should be enough for the migration but I receive error that disk space is not enough. Here is the query and error:

postgres=> ALTER TABLE "blocks" ADD COLUMN "textSearch" TSVECTOR GENERATED 
ALWAYS AS (to_tsvector('english', text)) STORED;
ERROR:  could not extend file "base/12974/446445": No space left on device
HINT:  Check free disk space.

Hello

Hello! I'm pretty new to SQL and I'm trying to figure out how to select a value from a nested JSON object and struggling to find out how to do so (unless my googling skills just suck) Below is an example of what I'm trying to do, I want to figure out how to select baz as I want to sort by this value

json
{
  "foo": {
    "bar": {
      "baz": 50
    },
    "something": null
  }
}

Hello

How can I create an RLS policy for

auth.uid()

is

user_id_one

OR

user_id_two

or in RLS

Thread

For anyone that has designed a production ready chat database, can I pick your brain on my approach? 🙂

Hey all! I've been racking my brain on this all morning. The function below is intended to create a function that returns a "shortkey" similar to the way YouTube handles video ids (

eTsiE3tmnI4

for example). The function is run as the default value on a column in my dB I'm getting some kind of syntax error, but there are little to no details in the error. Does anybody see anything wrong with it?? Thanks!!

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE OR REPLACE FUNCTION generate_shortkey()
RETURNS TEXT AS 
$$
DECLARE
  gkey TEXT;
  key TEXT;
  qry TEXT;
  found TEXT;
BEGIN
  -- generate the first part of a query as a string with safely
  -- escaped table name, using || to concat the parts
  qry := 'SELECT shortkey FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE shortkey=';

  LOOP
    -- 8 bytes gives a collision p = .5 after 5.1 x 10^9 values
    gkey := encode(gen_random_bytes(8), 'base64');
    gkey := replace(gkey, '/', '_');  -- url safe replacement
    gkey := replace(gkey, '+', '-');  -- url safe replacement
    key := rtrim(gkey, '=');          -- cut off padding

    -- Concat the generated key (safely quoted) with the generated query
    -- and run it.
    -- SELECT id FROM "test" WHERE id='blahblah' INTO found
    -- Now "found" will be the duplicated id or NULL.
    EXECUTE qry || quote_literal(key) INTO found;

    -- Check to see if found is NULL.
    -- If we checked to see if found = NULL it would always be FALSE
    -- because (NULL = NULL) is always FALSE.
    IF found IS NULL THEN
      -- If we didn't find a collision then leave the LOOP.
      EXIT;
    END IF;

    -- We haven't EXITed yet, so return to the top of the LOOP
    -- and try again.
  END LOOP;

  RETURN key
END
$$ language 'plpgsql';

Hey all I ve been racking my brain on