Using window functions in an update statement

sql django postgresql window-functions
jl6 picture jl6 · Dec 5, 2010 · Viewed 24.7k times · Source

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

Max picture Max · Dec 5, 2010

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.

Related questions

Strange PostgreSQL "value too long for type character varying(500)"

I have a Postgres schema which looks like: The problem is that whenever I save text longer than 500 characters in the description column I get the error: value too long for type character varying(500) In the documentation for Postgres it …

Read More
django test app error - Got an error creating the test database: permission denied to create database

When I try to test any app with command (I noticed it when I tried to deploy myproject using fabric, which uses this command): python manage.py test appname I get this error: Creating test database for alias 'default'... Got …

Read More
Why is iterating through a large Django QuerySet consuming massive amounts of memory?

The table in question contains roughly ten million rows. for event in Event.objects.all(): print event This causes memory usage to increase steadily to 4 GB or so, at which point the rows print rapidly. The lengthy delay before the …

Read More