https://supabase.com/ logo
Join Discord
Powered by
# sql
  • b
    I was able to fix it by doing the following 1. Creating a sequence using SQL editor 
    create sequence factura_id_seq;
    2. Update sequence start 
    ALTER SEQUENCE <your_sequence_name> RESTART WITH <max_id_in_table + 1>
    3. Update the table 
    ALTER TABLE factura ALTER id SET DEFAULT NEXTVAL('factura_id_seq');
    I'm not sure if this is the best way to solve it but if you have any other suggestions i'm happy to hear them thanks
  • p
    Noob question for plpgsql. How to format a string using printf-like semantics in a script? Basically I want the equivalent of 
    newstr := fmt.Sprintf("%s-%s", "foo", "bar")
    This has been surprisingly difficult to google.
    t
    • 2
    • 3
  • n
    I can't create type, can anyone help me?
    Copy code
    postgres=> CREATE TYPE public.service_type AS ENUM ('workout', 'recovery');
ERROR:  permission denied for table _field
CONTEXT:  SQL statement "truncate table graphql._field"
PL/pgSQL function graphql.rebuild_schema() line 3 at SQL statement
SQL statement "SELECT graphql.rebuild_schema()"
PL/pgSQL function graphql.rebuild_on_ddl() line 31 at PERFORM
postgres=>
  • d
    Hey, amazing updates during the launch week 4. Really exciting where Supabase is going! I have a small question regarding the RLS for my use case. I have a table 
    countries
    where each 
    country
    has a 
    people
    array of uuid. Every 
    county
    can have multiple 
    cities
    where each 
    city
    has one 
    county_id
    . I want to giver CRUD access to 
    cities
    for the 
    people
    . How can I set it up using the RLS? UPD: Added a SQL diagram in the thread.
    t
    • 2
    • 5
  • d
    Diagram
  • d
    Help: How do I generate 
    schema.grapghql
    from my Supabase Tables. Is there any command I can run to automatically generate the schema?
    s
    f
    +3
    • 6
    • 17
  • f
    can u try with 
    select graphql.rebuild_schema();
    ?
  • d
    But where do I find the output?
  • f
    @digitalsimboja oh, sorry I thought u want to force the schema rebuild on the DB... I don't found nothing on their docs related to your question... and propbably there isn't... 
    select * from graphql.type;
    in this view is the compiled schema, perhaps u can use that info to build the schema yourself
  • d
    Tried to run 
    yarn codegen:fetch
    as the closest call to fetch the schema from the Supabase table following this: https://github.com/supabase-community/supabase-graphql-example
  • f
  • f
    then u can add an script on 
    package.json
    Copy code
    "codegen:fetch": "node --no-warnings scripts/fetchGraphQLSchema ",
  • f
    can u execute the instroespetion query directly on the DB ?
  • f
    Copy code
    js
console.log(JSON.stringify({
      query: getIntrospectionQuery(),
    }), null, '  ');
  • f
    Copy code
    pgsql
select graphql.resolve($$
    <query> 
$$);
  • d
    I am still unable to query Supabase using the Graphql query.
  • d
    Probably I am setting up the query wrongly
  • s
    Move this to a thread please, this is the #869405720934744086 channel too so this would be better in the #843999948717555735 channel
  • s
    How do I generate a graphql schema from my tables
  • m
    Copy code
    dart
create or replace function calculate_current_balance(acc_name text)

returns numeric as $$

declare
  debit_total numeric;
  credit_total numeric;

begin
  select SUM(amount)
  into debit_total
  from transactions
  where transactions."debitAccount" = acc_name;


  select SUM(amount)
  into credit_total
  from transactions
  where transactions."creditAccount" = acc_name;



  if debit_total=null
  then
    debit_total=0;
  end if;
  if credit_total=null
  then
    credit_total=0;
  end if;

  return credit_total-debit_total;

end;

$$ language plpgsql
    When there is no row containing the 
    acc_name
    in any one of the two queries, it returns 
    null
    . To get rid of that, I added these if statements but I am still getting 
    null
    . Any idea how I can fix this?
    g
    • 2
    • 4
  • g
    testing for null
  • m
    Hi, I am trying to run an update in a stored procedure only if another update succeeded, but I am not sure how that works
    Copy code
    sql
    UPDATE TABLE_A
    SET col_a = CASE WHEN col_a + val_a < 11 
    THEN col_a + val_a 
    ELSE col_a END
    WHERE userid = auth.uid();
    
    IF FOUND THEN
      UPDATE TABLE_B
      SET col_b = col_b - val_b
      WHERE id = auth.uid();
    END IF;
    TABLE_A is updated only if col_a + val_a < 11 however TABLE_B always gets updated and I only want to update it if TABLE_A has been updated first. How would I do that? Thanks in advance
    t
    • 2
    • 1
  • t
    Update has a returning clause
  • b
    This query takes a long time to run (~15 seconds):
    Copy code
    SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
WHERE (SELECT count(*) FROM "Result" r WHERE r."typebotId" = t.id) >= 30
    Result table has 66,000 records. I'm wondering, is there a way to optimize this?
    t
    • 2
    • 12
  • t
    query optimisation
  • a
    Copy code
    sql
begin
  insert into public.UserFinanceData(id)
  values(new.id);

  return new;
end;
    g
    • 2
    • 21
  • a
    I am trying to create a trigger to insert a new row into my UserFinanceData table into the column named id with the value of the id from the users.auth table id column
  • a
    For whatever reason, anytime a new user is created Supabase does not insert a new row for that id, looking at this SQL trigger could there be anything causing a bug. This is really pissing me off
  • d
    Hello, I've gone through this entire channel trying to figure out why my update trigger isn't working, but none of the solutions seemed to help. I'm trying to sync my username from the auth.user's metadata with the public.profiles username column Here is the function I use for my trigger:
    Copy code
    begin
  if (old.raw_user_meta_data != new.raw_user_meta_data) then
    update public.profiles
    set username = new.raw_user_meta_data ->> 'username'
    where id = new.id;
  end if;
  return new;
end;
    And here is my simple auth.update function: 
    **const** { user, error } = await auth.update({ data: { username: username } });
    For some reason, this results in an error. I know it has to be something with the trigger/function, because the update is successful without it. Any ideas?
    g
    • 2
    • 3
  • g
    Hello I ve gone through this entire
1...383940...52Latest