Question 114919 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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;

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search