https://supabase.com/ logo
Join Discord
Powered by
# sql
  • b
    How to import openstreetmap data for pgrouting. Thanks
  • j
    Copy code
    select month, "total sales", sum("total sales") over(order by "month number" asc) as "running total" from 
(select to_char(date, 'Month') as month, sum(sale_price) as "total sales", extract(MONTH from date) as "month number"
from sales group by month, "month number") as sub_sales order by "month number" asc;
  • j
    Curious why that query is creating massive numbers for "running total"
  • t
    By any chance does your dataset have multiple years in it? Do you only want the last 12 months? I've tested your window function using an example from https://www.postgresql.org/docs/14/tutorial-window.html to create a mimimum reproduceable example and though it wasn't obvious at first I think you query should behave as you think except that it only would work if you only have month data that doesn't overlap with previous years.
    Copy code
    sql

--drop table empsalary;
create table empsalary as 
(
    SELECT
        "date", depname,empno,salary
    FROM (
        VALUES
            ('2022-01-01'::date, 'develop',11,5200),
            ('2022-02-01'::date, 'develop',7,4200),
            ('2022-03-01'::date, 'develop',9,4500),
            ('2022-04-01'::date, 'develop',8,6000),
            ('2022-05-01'::date, 'develop',10,5200),
            ('2022-01-01'::date, 'personnel',5,3500),
            ('2022-02-01'::date, 'personnel',2,3900),
            ('2022-01-01'::date, 'sales',3,4800),
            ('2022-02-01'::date, 'sales',1,5000),
            ('2022-03-01'::date, 'sales',4,4800)
    ) as data("date", depname,empno,salary)
);

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;



SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

SELECT salary, sum(salary) OVER () FROM empsalary;

-- this is kinda what you want right?
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

-- I'll adapt your query
--select month, "total sales", sum("total sales") over(order by "month number" asc) as "running total" from 
--(select to_char(date, 'Month') as month, sum(sale_price) as "total sales", extract(MONTH from date) as "month number"
--from sales group by month, "month number") as sub_sales order by "month number" asc;

-- using this sample data.

select 
month, "total sales", 
sum("total sales") over(order by "month number" asc) as "running total" from
(select to_char("date", 'Month') as month, sum(salary) as "total sales", extract(MONTH from "date") as "month number"
from empsalary group by month, "month number") as sub_sales order by  "month number" asc;

-- this prooves your window function is a likely correct as it looks okay.
  • j
    @User thank you so much for the follow up. I have put this on stack overflow, and as you pointed out. It is working as it should. The issue is that elephant SQL for some reason put the values in scientific notation. Though when I converted them to ints, they were exactly right!!!
  • t
    I'm assuming Elephant SQL is a postgres client? I haven't had a issue with my GUI client of choice though its a good reminder to use 
    psql
    Terminal to verify odd issues.
  • j
    yes, it is a client. what is psql? pgadmin?
  • t
    no 
    psql
    is a Terminal app where pgadmin is the GUI. Both maintained by the official Postgres community though.
  • o
    Hello please I need help. I'm getting an error when trying to return a custom record from this query.
    Copy code
    sql
  select type, total 
  from aggregate_business_transaction('b579f1b2-4c20-4d54-a3aa-4728b2ca32da') 
        as (type varchar, total int8)
    g
    • 2
    • 22
  • d
    I'm unsure what I'm doing what? This is the definition
    Copy code
    create or replace function get_user (p_user_id uuid) 
returns record
language plpgsql
as $$
begin
  SELECT ST_AsEWKT(location) AS location, *
  FROM public.users as u
  WHERE u.user_id = p_user_id
  LIMIT 1
end; $$
  • s
    If you're using the functions tab in the dashboard to add a new function, you only need the query between 
    begin
    and 
    end
    . You would only need everything before and after that if you were running it via the SQL editor.
  • d
    OOOh that makes sense. Since I'm essentially duplicating it
  • d
    Still an error. Missed a 
    ;
  • d
    I ran what I had via a query and it worked. Good catch. Thank you
  • d
    Okay maybe I'm a complete idiot. But how can I create a function for
    Copy code
    RETURN QUERY SELECT ST_AsEWKT(location) AS location, *
  FROM public.users
  WHERE user_id = p_user_id
  LIMIT 1;
    Where 
    p_user_id
    is the function param of type 
    uuid
    ?
    g
    • 2
    • 21
  • g
    function creation
  • s
    @User wanted to share a follow up from the team to https://discord.com/channels/839993398554656828/839993398554656831/951715562021593128 we're working on other features that took priority over SQL implementations of pg_graphql's C components. we don't have an ETA at the moment, and should be able to share more after current priorities wrap up
  • z
    Ok. Thanks for the update.
  • h
    Hey i have a basic doubt
  • h
    I have a table events, where 
    attended_by
    is a uuid
  • h
    In RLS i want the user who created the event so uid() = attended_by or the admin to be able to select the event
  • h
    The role of the user is given by another table so 
    SELECT users.role from users where users.id = uid()
    so now the role will have 
    'admin'
    or something else
  • h
    I couldn't use WITH to do this
  • h
    How do i do this?
  • h
    Copy code
    sql
WITH userInfo as (SELECT users.role from users where users.id = uid()) SELECT userInfo.role = 'admin' OR events.attended_by = uid() from userInfo, events
  • h
    This throws the error 
    Error updating policy: syntax error at or near "WITH"
  • h
    nvm
  • h
    I wrote it like this
  • h
    Copy code
    sql
((( SELECT users.role
   FROM users
  WHERE (users.id = uid())) = 'admin'::text) OR (attended_by = uid()))
  • s
    Hi, I'm working on a schema migration script that is supposed to update a hosted Supabase db instance with the latest changes in the schema. The idea is to spin up an empty db with the target schema by running: 1. supabase/postgres:latest via docker 2. apply the 4 init scripts found in the supabase repo 3. run my own schema.sql file 4. run migra to generate a migration script My problem is this: The migration script includes a bunch of SQL dealing with Supabase tables like storage and realtime, meaning my local instance is more "up to date" than my hosted version. How should I approach this? Is it safe to apply the changes, or do I need to reach out to the Supabase team to upgrade my hosted db?
1...363738...52Latest