Inserting data with a window function

Question

I'm looking for how to implement this answer https://stackoverflow.com/a/6821925/3067762

I have a table called window_test:

create table window_test (id int, seq int, data text, primary key (id, seq));

Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 seq    | integer | not null
 data   | text    | 

And i want seq to autoincrement for each id, for example

id |  seq   | data 
---+--------+-----------
 1     1       "whatever1"
 1     2       "whatever2"
 2     1       "whatever3"
 3     1       "whatever4"
 1     3       "whatever5"

I'm trying to use a window function to calculate "seq" while inserting the other data too. To provide the other data I'm using a CTE:

WITH temp_table (id, data) as ( values (1::int,'whatevertext1'::text))
INSERT INTO window_test (id, seq, data) SELECT id, ROW_NUMBER() OVER(PARTITION BY     id ORDER BY data), data FROM temp_table WHERE id = 1;

The query performs but the problem is that after the first insert it always violates the unique constraint of (id,seq). I think it's because ROW_NUMBER() is being calculated on my temporary table / CTE instead of window_test - how can I make it use the window_test table?

Answer 1

Hmmm, that's becuase you are starting over by "1" each time. You need to get the existing maximum value to avoid this problem. Here is one method:

WITH temp_table (id, data) as ( values (1::int,'whatevertext1'::text))
INSERT INTO window_test (id, seq, data)
    SELECT id,
           (ROW_NUMBER() OVER (PARTITION BY id ORDER BY data) + 
            COALESCE(wt.maxseq, 0)
           ),
           data
    FROM temp_table tt LEFT JOIN
         (SELECT id, MAX(seq) as maxseq
          FROM window_test
          GROUP BY id
         ) wt
         ON tt.id = wt.id
    WHERE id = 1;