Update with row_number() not working, why?

Question

I have the following table:

CREATE TABLE t_overview
(
  obj_uid uuid,
  obj_parent_uid uuid,
  obj_no integer,
  obj_text text,
  obj_path text,
  isdir integer,
  intid bigint,
  intparentid bigint
)

I want to move from uuid to bigint and created the new columns intid and intparentid. I need a unique integer (obj_uid is the primary key) for intid, so I just wanted to update with row_number() over (order by ...).

Did not seem to work. So I tried to write the results into a temp table, and update via join. But I got 1 for every intid.

But when I select from the join where I do the update, I get 1, 2, 3, 4, 5, 6 etc. What am I missing?

DROP TABLE IF EXISTS mytable;
CREATE TEMP TABLE mytable AS
WITH CTE AS 
(
    SELECT obj_uid, obj_parent_uid, obj_no
        , obj_text, obj_path, isdir
        , intid as cteIntId
        , intparentid as cteParentId
        , row_number() over (order by obj_uid) as rn 
    FROM T_Overview 
)
SELECT * FROM CTE;

UPDATE T_Overview SET intid = mytable.rn 
FROM T_Overview AS bt 
INNER JOIN mytable 
    ON mytable.obj_uid = bt.obj_uid 


-- UPDATE T_Overview SET intid = CTE.rn FROM CTE;
-- UPDATE T_Overview SET intparentid = CTE.intid FROM CTE;

Answer 1

@Frank already provided an explanation for your error.

But you don't need the temporary table at all:

BEGIN;
LOCK T_Overview;  -- if there is concurrent write access

WITH cte AS (
   SELECT obj_uid, obj_parent_uid
        , row_number() OVER (ORDER BY obj_uid) AS intid
   FROM   T_Overview 
   )
UPDATE  T_Overview t
SET     intid       = upd.intid 
      , intparentid = upd.intparentid
FROM   (
   SELECT t1.*, t2.intid AS intparentid
   FROM   cte t1
   LEFT   JOIN cte t2 ON t2.obj_uid = t1.obj_parent_uid
   ) upd
WHERE t.obj_uid = upd.obj_uid;

COMMIT;

The transaction wrapper and the explicit lock are only needed if there can be concurrent write access. (Even more so with a temp table, where you have a much bigger time slot in between.)

Assuming referential integrity - a FK constraint from T_Overview.obj_parent_uid to T_Overview.obj_uid. NULL values in obj_parent_uid are translated to NULL in intparentid.