PostgreSQL: How to Enter/Update data in a column for each unique customer and increment it by 1 until the end of Table

Question

I ask you to help me create a query (or Script) in the database (PostgreSQL). I want to write values in the "number" column, and so that it increases by 1 value for each unique client. I've searched all t he data on the Internet, but I still can't figure out how to do it right?

My db looks like this:

id	number	client_id	amount
193079	NULL	001	100900.00
193080	NULL	002	81900.00
193081	NULL	003	76400.00
193082	NULL	004	64800.00
193083	NULL	001	51200.00
193084	NULL	002	111348.57
193085	NULL	002	159962.87
193086	NULL	003	379492.86
193087	NULL	004	223358.30
193088	NULL	003	379492.86
193089	NULL	001	465358.30
193090	NULL	003	125358.30

What I want is to wryte single "Update" query or script that will modity my table like this:

id	number	client_id	amount
193079	1	001	100900.00
193080	1	002	81900.00
193081	1	003	76400.00
193082	1	004	64800.00
193083	2	001	51200.00
193084	2	002	111348.57
193085	3	002	159962.87
193086	2	003	379492.86
193087	2	004	223358.30
193088	3	003	379492.86
193089	4	001	465358.30
193090	4	003	125358.30

I don't quite understand, should I use sequence and nextval functinality/correctly? Even If, I can/must use "sequence and nextval", I don't know how to use them properly! Please help me!

I am using DBeaver to connect into my DB. I tried several types of Update:

UPDATE "myDatabase".myTable SET "number" =(CASE WHEN client = 001 THEN nextval(1) WHEN client = 002 THEN nextval(1) ... CASE WHEN number is not null THEN skip ELSE 'skip this row' END END );

Answer 1

If you want this as a one-off update, use row_number() window function and set up the window to be partitioned by client_id: demo

with cte as (select id,row_number()over w1 as number
             from your_table
             window w1 as (partition by client_id order by id) )
update your_table this
set number=cte.number
from cte
where this.id=cte.id;

select * from your_table order by id;

id	number	client_id	amount
193079	1	001	100900.00
193080	1	002	81900.00
193081	1	003	76400.00
193082	1	004	64800.00
193083	2	001	51200.00
193084	2	002	111348.57
193085	3	002	159962.87
193086	2	003	379492.86
193087	2	004	223358.30
193088	3	003	379492.86
193089	this should be 3	001 this is the 3rd 001	465358.30
193090	4	003	125358.30

Note the difference on your second row from the bottom: I think you wanted 3 in there.