Postgresql: Row Number Changes on Update

Postgresql: row number changes on update

the row number is changed

There is no such thing as a "row number" in a relational table.

I think when a row is updated, it should not be moved from its place

As a row doesn't have a "place", there is no such thing as "moving" it out of that either.

---

Rows in a relational table are not sorted.

Think of them like balls in a basket.

Without an ORDER BY the DBMS is free to return the rows in any order it thinks is OK.

If you need to have the rows ordered in a certain way the only (really: the only) way to get that is to use an ORDER BY statement.

SQL update records with ROW_NUMBER()

Let me assume that cards has a primary key. Then you can use join:

update cards c
    set position = c2.seqnum
    from (select c2.*, row_number() over () as seqnum
          from cards c2
         ) c2
    where c2.pkid = c.pkid;

I should note that the over () looks strange but Postgres does allow it. Normally an order by clause would be included.

Updating a table with row number in postgresql

If you still want to update the table from some source result set, you can use update ... from ... with join condition specified in where clause:

create table t
as
select q, null::int as w
from unnest(array[1,2,3]) as q

with s as (
  select row_number() over(order by q desc) as rn
    , ctid as row_id
  from t
)
update t
set w = rn
from s
where t.ctid = s.row_id

select *
from t

 q |  w
-: | -:
 3 |  1
 2 |  2
 1 |  3

db<>fiddle here

How to Update in Postgresql without affecting the row number?

A database table is an unordered set of tuples (also known as a relation), so you cannot rely on the order of rows returned from a SELECT * FROM tablename.

You need to enforce an ordering with an ORDER BY clause if you need it.

The internal reason why the location of a row changes after an update is that PostgreSQL actually writes a new version of the row, which in this case is appended at the end. But you cannot rely on that either: if there is free space in the middle of the table, the new row version can be added there.

Using Row_Number() with Update Statement

You are trying to update a column (countrow) that doesn't exist in your table so you will need to add this column to your original table and join to the subquery:

/* add the column */
ALTER TABLE usertable ADD countrow INT;

/* do the update */
UPDATE  usertable
SET     countrow = countrow_new
FROM ( 
        SELECT  userid, 
                createddate, 
                bookid, 
                pagenumber,
                CASE 
                    WHEN ROW_NUMBER() OVER (PARTITION BY userid, bookid ORDER BY bookid) = 1
                    THEN SUM(pagenumber) OVER (PARTITION BY userid, bookid) 
                END AS countrow_new
        FROM    usertable
      ) x
      JOIN usertable u
        ON u.bookid = x.bookid AND u.createddate = x.createddate AND u.userid = x.userid;

The above assumes {bookid,createddate,userid} will always be unique

However, storing calculations in a table this way is very bad design and bad practice - you will have to keep the column up to date with a trigger on every insert / update / delete which will be terrible for performance.

A better approach would be to take your original query and create a view for it which can then be used as though it was a table:

create the view:

CREATE VIEW vwMyView
AS
SELECT  userid, 
        bookid, 
        pagenumber,
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY userid, bookid ORDER BY bookid) = 1 
            THEN SUM(pagenumber) OVER (PARTITION BY userid, bookid) 
        END AS countrow
FROM    usertable;

Select from the view:

SELECT * FROM vwMyView;

How to update a column via Row_Number with a different value for each row?

You can update all the rows in one statement using a CTE as below.

;WITH T
     AS (SELECT IdDatoLegal,
                Row_number() OVER (ORDER BY IdCliente ) AS RN
         FROM   dbo.DatosLegales)
UPDATE T
SET    IdDatoLegal = RN 

How to update a column value with ROW_NUMBER() OVER value based on distinct values of a column

You can use dense_rank() for this purpose:

select name, id, dense_rank() over (order by minid) as seqno
from (select t.*, min(id) over (partition by name) as minid
      from table t
     ) t;

If you wanted to do this just with row_number():

select t.name, t.id, tt.seqnum
from table t join
     (select t.name, row_number() over (order by min(id)) as seqno
      from table t
      group by t.name
     ) tt
     on t.name = tt.name;

However, I don't know why you would want to do that.

---

Related Topics