I have a table with columns id,time,value STEP1: get the time value from the recent row i.e SELECT time FROM tablename ORDER BY id desc LIMIT 1 STEP2: get all the rows who are within last 5 minutes from this time. How can i do this Answer One method is a subquery: If this is really not the most recent
Tag: postgresql
trying to sum a column in postgres but trying to limit the results first?
I am trying to get data for the last 5 years within my database, then sum the results. Here’s what I’ve been trying: but I’m getting this error: when I comment out order by year desc, then the query works but I need it to start at the most recent year. also the limit doesn’t seem to do…
How to Aggregate 15 minute Time series into 1 hour intervals in Postgres
In my PostgreSQL database I have a very large (over 40 mil) row table that represents traffic counts for various locations by 15 minute interval timestamps (contained in the “starttime” column). I want to bin these intervals into hourly intervals to reduce the number of rows. The format is “…
Constraint on size of array type in Postgres
I have the following schema for a table (simplified for the sake of discussion) : A task transitions from NOT_SOLVED TO SOLVED when array_length(current_solved_by) == max_solved_by Questions: Is it a good practice to maintain another column, in this case state, which can be derived from other data (Based on s…
How to condense returned rows of users with multiple roles
I’m trying to wrap my head around this logic, but simply having a tough time. While trying to keep the database normalized, users can have multiple roles (example shows 2, but could have more than 2, currently have 6 total roles), and I’d like to return data to make it easily viewed on a front end UI. Tables …
How to filter PostgresSQL query result dependent on value on some column?
I have this query: Which results in this: Instead of returning all the 4 rows, I’d like to return the column which has a value “1”. How can I achieve this? Answer If you just want it to show the column with value = 1, you already have the answer. With mi answer you select all the columns wit…
Format JSONB column by taking the text value from same jsonb coulmn
CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values (‘dummydata-got-uploaded’); I need to correct the jsonb column value with below query. update test set data={“addDet”: data }::jsonb where id =1; ERROR: syntax error at or near “{” LINE 1: update test s…
Is there a way of computing numerical integration on PostgreSQL?
I’ve got a table with the following columns: timestamp, name and activePower. I wanted to compute power consumption based on that and add it to a Grafana line chart. Right now, what I’m doing is a cumulative sum, like the following: Unfortunately, the intervals of timestamp are not regular, and I …
Update data based on the previous row in postgresql
For rn (row number) = 1, x and y always stays as 0. Now I need to update the rest of x and y columns based on the above scenario(IF ..ELSE). I am unable to update the next row without updating the previous row. Is there a way to do this in a query Answer You can use a recursive
Why am I getting a cross-reference error with my insert/select statement in single database
I have the following INSERT statement, and it works fine without the WHERE clause, but will not work with it. There is a separate database that I have connected to via a dblink connection, and I have created several views that contain data I will need for the database I’m working in. I have the followin…