Using window functions in an update statement

Question

I have a large PostgreSQL table which I access through Django. Because Django's ORM does not support window functions, I need to bake the results of a window function into the table as a regular column. I want to do something like this:

UPDATE  table_name SET     col1 = ROW_NUMBER() OVER ( PARTITION BY col2 ORDER BY col3 ); 

But I get ERROR: cannot use window function in UPDATE

Can anyone suggest an alternative approach? Passing the window function syntax through Django's .raw() method is not suitable, as it returns a RawQuerySet, which does not support further ORM features such as .filter(), which I need.

Thanks.

Answer 1

The error is from postgres not django. You can rewrite this as:

WITH v_table_name AS (     SELECT row_number() over (partition by col2 order by col3) AS rn, primary_key     FROM table_name )  UPDATE table_name set table_name.col1 = v_table_name.rn FROM v_table_name WHERE table_name.primary_key = v_table_name.primary_key;   

Or alternatively:

UPDATE table_name set table_name.col1 = v_table_name.rn FROM   (     SELECT row_number() over (partition by col2 order by col3) AS rn, primary_key     FROM table_name ) AS v_table_name WHERE table_name.primary_key = v_table_name.primary_key; 

This works. Just tested it on postgres-9.6. Here is the syntax for UPDATE (see the optional fromlist).

Hope this helps.