59

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.

SteveC
  • 15,808
  • 23
  • 102
  • 173
jl6
  • 6,110
  • 7
  • 35
  • 65

1 Answers1

99

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 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 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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
Max
  • 3,371
  • 2
  • 29
  • 30
  • I don't think you need the self join? I might well be wrong though as I'm basing this on what works in SQL Server! – Martin Smith Dec 05 '10 at 15:07
  • @Martin: You are selecting two table, one is through UPDATE `tablename` and another is in the `FROM tablename` clause. if you don't join explicitly, its going to produce a Cartesian product. – Max Dec 05 '10 at 16:38
  • First thing: I am NOT an sql guru. I might be wrong. Please correct me if so. Direct from the manual:http://www.postgresql.org/docs/current/static/sql-update.html from_list:A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list). – Max Dec 05 '10 at 17:34
  • This is why I thought, without the self join its going to generate Cartesian product. This might be postgres specific, but as far as I know, postgres is the most standard complaint RDBMS. – Max Dec 05 '10 at 17:36
  • @mamnun - I just tested in postgres and it seems it doesn't support updatable `cte` s anyway. In SQL Server you can update the cte itself as in this example http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by/3439180#3439180 and SQL Server will treat the CTE as though it were an updatable view and update the base table. – Martin Smith Dec 05 '10 at 19:48
  • 1
    now I have to eat my words :( yep, postgres doesn't have any update-able view. Rules are needed to be created for an updatable view to work. – Max Dec 05 '10 at 20:12
  • 7
    I needed to omit the `table_name.` part from the `SET` statement, but otherwise this worked great. – Matthew Schinckel Nov 30 '15 at 23:19
  • I also had to omit the table_name. part from the SET statement in Postgres – allthesignals Dec 07 '15 at 18:22
  • (In CartoDB) I had to put double quotes around the column name. So I changed table_name.col1 to "col1" Success! – Marjorie Roswell Feb 13 '16 at 14:57
  • I had no error using the CTE form in PostgreSQL 9.6 – raphael May 15 '17 at 17:04
  • @Max, thanks for the input. I imitated your code but the window function does not seem to loop through. Any idea? `WITH v_table AS ( SELECT CONCAT('_s', CAST(DIV(ROW_NUMBER() OVER (PARTITION BY epic_simulation_results."HUC_10"), 47.1) + 1 as varchar)) as sc_id FROM glbrc_other_experiments.epic_simulation_results ) UPDATE glbrc_other_experiments.epic_simulation_results set scenario_id = v_table.sc_id FROM v_table;` – yearntolearn Jun 01 '21 at 21:16