Insert a unique key, a couple of columns and the sum of sub-query into a table

Question

I am using postgres DB and, in the middle of a DB migration, I have an empty table tableB which I want to fill from data existing in another tableA.

tableB has the following columns

CREATE TABLE contributors (
    id bigint NOT NULL,
    pps double precision NOT NULL,
    contributor_user_id bigint,
    project_id bigint
);

while tableA has the following columns

CREATE TABLE tableA (
    id bigint NOT NULL,
    assigned_ppoints double precision NOT NULL,
    state integer,
    contributor_id bigint,
    project_id bigint
);

All *_id are actually foreign keys.

I need to add one new row on tableB for each existing combination of contributor_id and project_id of tableA as follows

• in project_id, project_id of tableA
• in contributor_user_id, I need contributor_id of tableA
• in pps i need the sum of assigned_ppoints of that contributor_user_id and project_id for which state=2 within tableA.

My starting (and very distant) point is

INSERT INTO tableB (id, project_id, contributor_user_id, pps) 
SELECT MAX(id) FROM tableB, project_id, contributor_id, SUM(assigned_ppoints)
FROM tableA WHERE project_id=1 AND state=2 AND contributor_id=1;

Which is wrong, and would only add one row corresponding to one combination of project_id and contributor_id.

How can I do this?

Answer 1

Aggregate filter:

select
    max(id),
    contributor_id,
    project_id,
    sum(assigned_ppoints) filter (where state = 2)
from t
group by 2,3

For 9.3 and previous versions:

select
    max(id),
    contributor_id,
    project_id,
    sum(assigned_ppoints * (state = 2)::integer)
from t
group by 2,3

Answer 2

I'd suggest a structure like this:

insert into A (...)
select
  something_B,
  another_B,
  (select something_C from C where ...) as subselect_C
from B
where ...
;

As you see now you'd do a subquery for every matching row of B.