How to update table with field Postgres

Question

I have table like this:

Incident_id is foreign key. What I want to achieve it -> create one more column with named position (int). And populate it like this: find all rows for each incident_id and and update each row with index or list of rows I get by each incident_id. exapmple: incident_id 5 matches with 4 note rows so updated for the position will be 0, 1, 2, 3 accordingly. Ty

Answer 1

I would not recomment storing such derived information. Instead, you can create a view that uses row_number() to enumerate the rows of each incident_id:

create view myview as
select t.*, row_number() over(partition by incident_id order by id) - 1 rn
from mytable t

To get a stable result, you need a column that can be used to consistently order the rows of each incident: I called it id in the query. You can change that to the relevant column name (or set of columns) for your use case; you would typically use the primary key column(s) of your table.

---

EDIT

If you really wanted to materialize that value in a new column, and considering that the primary key of your table, you would do:

alter table mytable add column position int;

update mytable t
set position = t1.position
from (
    select incident_note_id, 
        row_number() over(partition by incident_id order by incident_note_id) - 1 position
    from mytable
) t1
where t1.incident_note_id = t.incident_note_id;