Atomic aggregate-and-replace in PostgreSQL

In PostgreSQL, to aggregate rows in a table and replace those rows with the results in an atomic way, you can use a WITH query, also known as a Common Table Expression or “CTE”.

Let’s set up a simple table and insert some data.

    # CREATE TABLE t (name TEXT, amount INTEGER);                          
    CREATE TABLE                                                                   
                                                                                   
    # INSERT INTO t VALUES ('foo', 12), ('foo', 23),                       
    #     ('foo', 45), ('bar', 5), ('bar', 6);                             
    INSERT 0 5                                                                     
                                                                                   
    # SELECT * FROM t;                                                     
    name | amount                                                                  
    ------+--------                                                                
    foo  |     12                                                                  
    foo  |     23                                                                  
    foo  |     45                                                                  
    bar  |      5                                                                  
    bar  |      6                                                                  
    (5 rows)                                                                       

We would like to sum the amount for each name and replace the summed rows with the results. To avoid race conditions, we would like to do this in one query. We will use a CTE to define a table that exists just for this one query. We will delete the original rows, put them in the temporary table, then insert new rows with the aggregated data — and all in one query!

    # WITH mytemp AS (DELETE FROM t RETURNING name, amount)                          
       INSERT INTO t (name, amount)                                                
           SELECT   name, sum(amount)                                              
           FROM     mytemp                                                         
           GROUP BY name;                                                          
                                                                                   
    # SELECT * FROM t;                                                     
    name | amount                                                                  
    ------+--------                                                                
    foo  |     80                                                                  
    bar  |     11                                                                  
    (2 rows)                                                                       

Pin It on Pinterest