Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

/*
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:
*/
create table your_table (
  id int primary key, 
  number int, 
  client_id text, 
  amount numeric);
insert into your_table values
(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)
returning *;
--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:



postgresqlmultiple-columnsdbeaverEdi
*/

CREATE TABLE




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



INSERT 0 12


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);

ERROR:  syntax error at or near ".."
LINE 4:                     ... 
                            ^


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;

UPDATE 12




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
3
001
465358.30

193090
4
003
125358.30



SELECT 12

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