So as the title suggests I need to return the records from a table, where these records can belong to a group. If there are several records in a group, return only the last one, and if the record does not belong to any group, return it together. I have the following tables (automation_execution) 1 –>…
Tag: postgresql
Use PostreSQL 9.3 to calculate Monthly Recurring Revenue (MRR), by customer
I’ve found a few answers that can get me close here and here, but not exactly what I’m looking for. Here’s the sample data set start end custid amt 2021-02-01 2022-01-31 1 10 2021-03-01 2021-03-31 2 20 The ideal output would generate the series for each customer like the table below month cu…
syntax error at or near “INSERT” when creating a temp table and insert into a subquery–PostgreSQL Error
I created a temp table and insert into a subquery but I got an error saying ‘syntax error at or near “INSERT”‘. Can anyone have idea? Thanks! Answer Note that CREATE TABLE and INSERT INTO are two distinct statements. You need to insert a ; between them: I created a temp table Note that…
PostgreSQL verify an empty array on json
I have the following row on select I have to Identify empty jsons, then manually add a value to it (eg row 1 and 3 ), I tried the following : But the “is null” verification fails for this type of data (array of json), how to identify ‘[]’ values in this case? Note: I only have select p…
How to sum rows in groups of 3?
I have a table that looks like this: I need a query that returns the summed amount of rows grouped by every 3 consequent IDs. The result should be: In my table, you can assume IDs are always consequent. So there can’t be a 10 without existing a 9 too. But the same ID can also show up multiple times
Select row where latest quarter and year In POSTGRESQL
I need help with my issue here . I need to select rows where quarter and year are latest . Here is example of my table Risk Master table named HD_Risk_Master:- Another table that join with HD_Risk_Master table named HD_Case_Resolution 1:M Expected Result My result of query , no HDS-OP3 and HDS-OP4 in my query…
Idempotent record creation: is it better to use a unique constraint or check for existence before inserting a record?
I’ve recently been wondering what’s generally the best approach for ensuring that the creation of database records is idempotent. The two approaches I can think of are: Checking whether a record already exists before executing an INSERT Using a unique constraint on the relevant columns to insure t…
If there are several Positions, then find only the main one, otherwise take another
Need to filter the records so that the employee mostly has only the position with the employment type 0. I can’t just do where TypeEmployment = 0, because it will cut off the employee with private face id 3406 But I would like to see him, because he does not have a position with type 0 However, the empl…
Why is this Index Scan so slow?
From what I can see, the index is not entirely loaded up in memory, and that’s what causing it to be slow? Am I correct? obs: I changed the “Output” section a little bit for privacy concerns Answer The index scan is fast (1.774 milliseconds on average). The execution is so slow because the i…
PostgreSQL check if values in a given list exist in a table
Given below table in Postgres: id some_col 1 a 1 b 2 a 3 a I want to get output as id and true (if at least one row with that id is present in the table) or false (if no rows with that id are found in the table). For example where id in (1, 2, 3, 4, 5):