postgresqlgroup-bypostgresql-9.5full-outer-join

Full Outer Join on two columns is omitting rows


Some background, I am making a table in Postgres 9.5 that counts the number of actions performed by a user and grouping these actions by month using date_trunc(). The counts for each individual action are divided into separate tables, following this format:

Feedback table:                   
id  |  month  |  feedback_counted 
----+---------+-------------------
 1  |    2    |         3         
 1  |    3    |        10         
 1  |    4    |         7         
 1  |    5    |         2         

Comments table:
id  |  month  |  comments_counted
----+---------+-------------------
 1  |    4    |         12
 1  |    5    |          4
 1  |    6    |         57
 1  |    7    |         12

Ideally, I would like to do a FULL OUTER JOIN of these tables ON the "id" and "month" columns at the same time and produce this query:

Combined table:
id  |  month  |  feedback_counted  |  comments_counted
----+---------+--------------------+-------------------
 1  |    2    |         3          |         
 1  |    3    |        10          |        
 1  |    4    |         7          |        12
 1  |    5    |         2          |         4
 1  |    6    |                    |        57
 1  |    7    |                    |        12

However, my current query does not capture the feedback dates, displaying it like such:

Rollup table:
id  |  month  |  feedback_counted  |  comments_counted
----+---------+--------------------+-------------------    
    |         |                    |       
    |         |                    |       
 1  |    4    |         7          |        12
 1  |    5    |         2          |         4
 1  |    6    |                    |        57
 1  |    7    |                    |        12

This is my current statement, note that it uses date_trunc in place of month. I add the action counts later, the main issue is somewhere here.

CREATE TABLE rollup_table AS 
SELECT c.id, c.date_trunc
    FROM comments_counted c FULL OUTER JOIN feedback_counted f 
    ON c.id = f.id AND c.date_trunc = f.date_trunc
GROUP BY c.id, c.date_trunc, f.id, f.date_trunc;

I'm a bit of a novice with SQL and am not sure how to fix this, any help would be appreciated.


Solution

  • Replace ON c.id = f.id AND c.month = f.month with USING(id, month).

    SELECT id, month, feedback_counted, comments_counted
    FROM comments c 
    FULL OUTER JOIN feedback f 
    USING(id, month);
    
     id | month | feedback_counted | comments_counted 
    ----+-------+------------------+------------------
      1 |     2 |                3 |                 
      1 |     3 |               10 |                 
      1 |     4 |                7 |               12
      1 |     5 |                2 |                4
      1 |     6 |                  |               57
      1 |     7 |                  |               12
    (6 rows)
    

    Test it in db<>fiddle.