PostgreSQL: how can I Alter table by setting a column of the selected rows to be increasing integers?

Question

I have this table named issue with thousands of rows:

id	status	listPosition
43	New	0
44	New	0
45	New	0
46	Accepted	0
51	Accepted	1
54	Accepted	0
66	processing	0
68	processing	0

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 :

id	status	listPosition
43	New	1
44	New	2
45	New	3
46	Accepted	1
51	Accepted	2
54	Accepted	3
66	processing	1
68	processing	2

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?

The error is telling you the problem, there is no column "dummy.listPosition" as double quoting the whole thing turns it into a single identifier instead of what you wanted which was "dummy"."listPosition". The CTE 'table' "dummy" and the 'table' column "listPosition". See Identifiers. — Adrian Klaver
– Adrian Klaver, Commented Aug 29, 2022 at 16:57
@jian this is not enough though, the finally working code is SET "listPosition"= dummy."listPosition" — Kid_Learning_C
– Kid_Learning_C, Commented Aug 30, 2022 at 3:00

DannySlor · Accepted Answer · 2022-08-29 12:43:57Z

2

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

id	status	listposition
43	New	1
44	New	2
45	New	3
46	Accepted	1
51	Accepted	2
54	Accepted	3
66	processing	1
68	processing	2

Fiddle

edited Aug 29, 2022 at 12:43

answered Aug 29, 2022 at 12:36

DannySlor

4,6201 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kid_Learning_C Over a year ago

this only shows the result, but does not actually change the column value though.

Kid_Learning_C Over a year ago

please check my updated question

DannySlor Over a year ago

sorry, busy day. I'll get to it by tonight.

Kid_Learning_C · Accepted Answer · 2022-08-30 03:01:46Z

0

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;

answered Aug 30, 2022 at 3:01

Kid_Learning_C

3,7256 gold badges58 silver badges128 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: how can I Alter table by setting a column of the selected rows to be increasing integers?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related