Tag: postgresql

Complicated PostgreSQL query to group by multiple columns

I have PostgreSQL table with client_id, date, smp_number, vs_type, vs_amount: What I want to achieve is to group by client_id and month and then to get the total debit sum and the total credit sum by month in distinct columns and the last smp_number in each month. The result should be: Hope this makes sense, …

Laravel Row duplication inserted, with updateOrCreate method with Race-Condition

duplicates laravel postgresql race-condition sql

i have function in my controller that create a forecast : As you can see i use updateOrCreate methods to add or update a forecast. The problem is when 2 requests from the same user run at the same time (and no forecast is already created) 2 row are inserted. Do you have a solution ? I See that the

Import dump file containing a database dump to dbeaver

dbeaver postgresql sql

I have a database dump in thisdb_2022.dump binary file that I’m trying to import to dbeaver, but I haven’t found a way to import the database so I can see it. I found the below in the dbeaver forum but when I try to follow the instructions and create a new connection I don’t see any option I…

UNION ALL Slower than N queries

greatest-n-per-group node.js postgresql sql union-all

This question is following this question where I wanted to select the MAX value of multiples fields while retrieving each row. The accepted answer with UNION ALL worked like a charm but I now have some scaling issues. To give some context, I have more than 3 million rows in my matches table and the filters us…

Select the row with the most recent modified date

greatest-n-per-group postgresql sql sql-order-by

I have this table: user_id name email modified_date 1 John a@gmail.com 2022-01-01 1 John b@gmail.com 2022-01-02 1 Lucy c@gmail.com 2022-01-03 2 Joey d@gmail.com 2021-12-24 3 Mike e@gmail.com 2022-01-01 3 Mary f@gmail.com 2022-01-02 I’m trying to get unique user_id’s email with the most recent modi…

get all students who has got more marks than the average marks of that class

postgresql sql

I got two tables below called tbl_student where we store students’ data like id, name, class, roll no. and age, and tbl_marks where we store marks scored in an exam. – and And these are some data for the respective tables: and Now, I need to get all students in each class who got more marks than t…

Calculate the difference of dates in one column

postgresql sql

DB-Fiddle Expected result: I want to calculate the date_difference between the MAX and the MIN order_date per campaign. So far I was able to come up with this query: This solution would work if the dates would be in two separate columns. How do I have to modify the query to also make it work if they are in on…

SQL Joins not working correctly when no data present

java postgresql sql

I’ve got the following code, where I build a tag from an entity Each tag has an id, name, description and how many questions it has, how many it has in a day, and how many it has in a week. The problem is, I can’t seem to display tags that have no questions tied to them, and also if

Why does this SQL query get stuck in an endless loop?

postgresql query-optimization sql

The following PostgreSQL query gets stuck loading endlessly. I know correlated sub-queries can take long, but a SELECT using the same parameters worked quickly and returned the desired results. And I have a small data set that I let run for an entire day just to make sure it wouldn’t eventually work wit…

How do combine two Queries or is it only one?

postgresql postgresql-13 sql

Question: How do combine two Queries or is it only one? Example: I have two exact similar tables my PostgreSQL Database. info_table and info_table_dump with the exact same columns date_at, name, color and length . Now i want to know if there are entries in info_table_dump that do not exist in info_table. Ther…