Given a postgres table of the following form: group id | timestamp | value ———+——————————-+—— 1 | 2020-04-15 15:04:44.020288+00 | 8.0 2 …
Tag: postgresql
SELECT FOR UPDATE becomes slow with time
We have a table with 1B entries and there are 4 processes which work on this simultaneously. They claim rows with their session ids with 1000 rows at a time and then update the table after 10,000 rows….
Print rows of stars using PostgreSQL?
I am trying to replicate the following code in SQL Server to PostgreSQL: Required output Answer You can use generate_series() Online example
Update to remove double quotes from number jsonb postgresql
I am using PostgreSQL and I am trying to run an update for jsonb. I want “2W” to change to just the number 2. So the query below removes the W, but leaves it as “2”. How I would go about removing the double quotes ? currently it looks like {“size”: “2W”} and I w…
Syntax for counting greater than and summing a value with multiple joins without the keyword having
I’m learning SQL (Postgres) and I’m slightly confused about something I’m trying to do. I have the following tables. Employee: Project: Department: And works_on: For this, I was trying to do something slightly more complex: To create a view that has project name, department name, number of e…
check for same range in postgresql
I wanted to check if for one particular student my column (Amount) contains a value less than 10, and if yes, then check if for the same student my column (Amount) is also greater than 10 and update the column new accordingly in PostgreSQL. MY table: Tried this way but not working The output I’m expecti…
Add column data to a table based on geometry intersection with another table – PostgreSQL
my city has defined some Urban Revitalization Perimeters using PostGIS I’ve separated the city Blocks within Perimeters and the Buildings within Perimeters. What I want to do now is to update the buildings table with two new columns, that relates them with the blocks they are on. The following is a samp…
PostgreSQL – error when performing not in
here is the table: create table video_call ( timestamp timestamp, callerid int, receiverid int, call_length int); insert into video_call values (‘2018-12-12 01:01:01’, 1, 2, 3), (‘2019-01-01 01:01:…
Reducing granularity of historical status table?
I have a table with what are essentially historical logs for project tasks. Each row contains the ID for a project and the date of when one specific task was either started or ended. I need to reduce the grain so that all of the start/end times are in a single row (see IDs 1 and 2 in the image)
PostgreSQL: Bad query performance caused by unused index?
we have a query to get all jobs with changes inside a certain time period. Depending on the selected period the performance goes from