I have this table named issue with thousands of rows:

I want to select all rows whose status is a specific value, like New, and update the listPosition value such that they are in increasing indexes, like 1, 2, 3, ....

the result should be like :

How can I do that in PostgreSql?

I tried:

UPDATE issue SET listPosition= 'dummy.listPosition'
FROM  
(
  SELECT id, row_number() over (partition by status order by id) AS "listPosition" FROM issue
) AS dummy
WHERE issue.id= 'dummy.id';

and it gives this error:

ERROR:  invalid input syntax for type integer: "dummy.id"
LINE 6: WHERE issue.id= 'dummy.id';

How did it fail?

I tried both: (don't worry about the dstOrgId thing)

UPDATE issue SET listPosition= "dummy.listPosition"
FROM  
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) AS dummy
WHERE issue.id = dummy.id;



WITH dummy AS
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) 
UPDATE issue set listPosition = "dummy.listPosition"
FROM dummy
WHERE issue.id = dummy.id; 

They all gives error:

ERROR:  column "dummy.listPosition" does not exist
LINE 5: UPDATE issue set listPosition = "dummy.listPosition"

Why on earth does it not exist??? PostgreSQL syntax is so freaking strange, since it asks me to change set listPosition = dummy.listPosition to set listPosition = "dummy.listPosition", and complains that column does not exist.

what????

I have finally got the working command through trial and error:

UPDATE issue SET "listPosition"= dummy."listPosition"
FROM  
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) AS dummy
WHERE issue.id = dummy.id;

what is the significance of double quote and the difference between single quote and double quote in PostgreSQL anyways?

CodePudding user response：

You can achieve it by using row_number.

select   id
        ,status
        ,row_number() over (partition by status order by id) as listPosition
from     t 
order by id, status

Fiddle

CodePudding user response：

I have finally got the working command through trial and error:

UPDATE issue SET "listPosition"= dummy."listPosition"
FROM  
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) AS dummy
WHERE issue.id = dummy.id;