UPDATE column using a window function

Question

I am using PostgreSQL 9.3

I have a table named cat with the three following columns of interest:

ID, SOURCE, TIME

ID and TIME values are unique (i.e. no duplicates) but several rows have the same SOURCE value

I would like to update each value of the SOURCE column, setting it to the ID value of the first input row in each group of rows having the same SOURCE value and ordered in TIME ascending.

In a SELECT statement, I would use:

SELECT
first_value(ID) OVER (PARTITION BY SOURCE ORDER BY TIME ASC) AS SOURCE
FROM cat;

So I tried this for the UPDATE statement:

UPDATE cat
SET SOURCE = first_value(ID) OVER (PARTITION BY SOURCE ORDER BY TIME ASC);

Which returns the following error: ERROR: window functions are not allowed in UPDATE

Could someone help me to find a fast way of doing this given that cat has ~800 000 rows and 322 columns?

user_10676716 · Answer

You could use a common table expression to compute the new value, and then apply the new value to the original table, like:

with cte as (
    select      
        id, 
        time,
        first_value(id) over (partition by source order by time asc) as source
    from cat
)
update cat set cat.source = cte.source
from cte
where cte.id = cat.id and cte.time = cat.time

user_1144035 · Answer

I would phrase this using aggregation (in essence) rather than a window function:

UPDATE cat c
    SET SOURCE = cc.ID
    FROM (SELECT DISTINCT ON (SOURCE) c.*
          FROM cat
          ORDER BY SOURCE, TIME
         ) cc
    WHERE cc.SOURCE = c.SOURCE;

This is safer because it does not make any assumptions in the data about 1-1 matches on the rows -- your question does not specify any primary key on the table. It might also have better performance with an index on (SOURCE, TIME); however, that might depend on the distribution of values in the data.

The above does not handle NULL values for SOURCE, but that is easily handled:

    WHERE cc.SOURCE IS NOT DISTINCT FROM c.SOURCE;

UPDATE column using a window function

2 Answers