Is it possible to get list of all id's that are grouped by-CodePudding

WE have a springboot application and there a scenario where we are using native query to insert data into a table based on the groupby query on another table. Now, I need to get the list of all the id's of the records that are grouped by before insert.

Query:

insert into table2 (column1,column2) 
select column1,column2 
from table1 
group by column1,column2

Now, I need to get the list of id's that are grouped in table1. Is it possible?

CodePudding user response：

With only access to table 2 you cannot get the ids what were grouped in table1, unless column1 or column2 is the id itself.

CodePudding user response：

Here is an example where we have a marker in the users table to know whether the id's have been summarised. The function returns the list of id's to summarise, inserts the information into the table summary and marks the id's as inserted.
This does not account for find more users created in a month already summarised (it will create a second row for the same month). It could be modified to update if the month year is found, but it demonstrates what I understand you are trying to achieve.

create table users (
id int,
created date,
summarised int default 0);
insert into users (id, created)values
(1,'2020-01-01'),
(2,'2020-01-15'),
(3,'2020-02-15');
create table summary(
year int,
month int,
num_id int);

✓

3 rows affected

✓

CREATE FUNCTION Summarise()
    RETURNS TABLE (ID int)
    LANGUAGE plpgsql AS
  $BODY$
      BEGIN
           RETURN QUERY
              SELECT users.id from users where summarised = 0;
 
        insert into summary
           select 
                date_part('year',users.created),
                date_part('month',users.created)
                ,count(users.id)
           from users 
           where users.summarised = 0
           group by date_part('year',users.created), date_part('month',users.created);
           
           
        UPDATE users set summarised = 1;
      END;
  $BODY$

✓

select * from users;
select * from summary;
select Summarise();
select * from users;
select * from summary;

id | created    | summarised
-: | :--------- | ---------:
 1 | 2020-01-01 |          0
 2 | 2020-01-15 |          0
 3 | 2020-02-15 |          0

year | month | num_id
---: | ----: | -----:

| summarise |
| --------: |
|         1 |
|         2 |
|         3 |

id | created    | summarised
-: | :--------- | ---------:
 1 | 2020-01-01 |          1
 2 | 2020-01-15 |          1
 3 | 2020-02-15 |          1

year | month | num_id
---: | ----: | -----:
2020 |     1 |      2
2020 |     2 |      1

db<>fiddle here