Postgres 16

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

create table books(
  id bigint generated by default as identity primary key,
  account_id bigint not null,
  identifier bigint not null);

insert into books(account_id,identifier) values
(7,13),(7,14),(7,14),(7,15),(111,156),
(111,155),(111,198),(111,178),(111,167),(111,196),(111,156),(111,150),(111,223),
(111,155),(111,198),(111,178),(111,167),(111,196),(111,156),(111,150),(111,223);

select * from books order by identifier;

CREATE TABLE



INSERT 0 21




id
account_id
identifier


1
7
13

2
7
14

3
7
14

4
7
15

12
111
150

20
111
150

14
111
155

6
111
155

11
111
156

5
111
156

19
111
156

9
111
167

17
111
167

16
111
178

8
111
178

18
111
196

10
111
196

15
111
198

7
111
198

21
111
223

13
111
223



SELECT 21


select account_id, identifier, count(*)
from books                                           
group by account_id, identifier   
HAVING count(*) > 1
order by identifier;


account_id
identifier
count


7
14
2

111
150
2

111
155
2

111
156
3

111
167
2

111
178
2

111
196
2

111
198
2

111
223
2



SELECT 9


UPDATE books
SET identifier = new_identifier
FROM (
  SELECT
    id,
    (
      SELECT MAX(identifier) FROM books WHERE account_id = dup.account_id
    ) + (
      ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY id)
    ) AS new_identifier
  FROM books dup
  WHERE EXISTS(
    SELECT *
    FROM books orig
    WHERE dup.account_id = orig.account_id
      AND dup.identifier = orig.identifier
      AND orig.id < dup.id
  )
) to_update
WHERE books.id = to_update.id
returning *;


id
account_id
identifier
id
new_identifier


3
7
16
3
16

11
111
224
11
224

14
111
225
14
225

15
111
226
15
226

16
111
227
16
227

17
111
228
17
228

18
111
229
18
229

19
111
230
19
230

20
111
231
20
231

21
111
232
21
232



UPDATE 10


select account_id, identifier, count(*)
from books                                           
group by account_id, identifier   
HAVING count(*) > 1
order by identifier;


account_id
identifier
count



SELECT 0


select account_id, identifier, count(*)
from books                                           
group by account_id, identifier   
--HAVING count(*) > 1
order by identifier;


account_id
identifier
count


7
13
1

7
14
1

7
15
1

7
16
1

111
150
1

111
155
1

111
156
1

111
167
1

111
178
1

111
196
1

111
198
1

111
223
1

111
224
1

111
225
1

111
226
1

111
227
1

111
228
1

111
229
1

111
230
1

111
231
1

111
232
1



SELECT 21


select * from books order by identifier;


id
account_id
identifier


1
7
13

2
7
14

4
7
15

3
7
16

12
111
150

6
111
155

5
111
156

9
111
167

8
111
178

10
111
196

7
111
198

13
111
223

11
111
224

14
111
225

15
111
226

16
111
227

17
111
228

18
111
229

19
111
230

20
111
231

21
111
232



SELECT 21

id	account_id	identifier
1	7	13
2	7	14
3	7	14
4	7	15
12	111	150
20	111	150
14	111	155
6	111	155
11	111	156
5	111	156
19	111	156
9	111	167
17	111	167
16	111	178
8	111	178
18	111	196
10	111	196
15	111	198
7	111	198
21	111	223
13	111	223

account_id	identifier	count
7	14	2
111	150	2
111	155	2
111	156	3
111	167	2
111	178	2
111	196	2
111	198	2
111	223	2

id	account_id	identifier	id	new_identifier
3	7	16	3	16
11	111	224	11	224
14	111	225	14	225
15	111	226	15	226
16	111	227	16	227
17	111	228	17	228
18	111	229	18	229
19	111	230	19	230
20	111	231	20	231
21	111	232	21	232

account_id	identifier	count
7	13	1
7	14	1
7	15	1
7	16	1
111	150	1
111	155	1
111	156	1
111	167	1
111	178	1
111	196	1
111	198	1
111	223	1
111	224	1
111	225	1
111	226	1
111	227	1
111	228	1
111	229	1
111	230	1
111	231	1
111	232	1

id	account_id	identifier
1	7	13
2	7	14
4	7	15
3	7	16
12	111	150
6	111	155
5	111	156
9	111	167
8	111	178
10	111	196
7	111	198
13	111	223
11	111	224
14	111	225
15	111	226
16	111	227
17	111	228
18	111	229
19	111	230
20	111	231
21	111	232

id	account_id	identifier
1	7	13
2	7	14
3	7	14
4	7	15
12	111	150
20	111	150
14	111	155
6	111	155
11	111	156
5	111	156
19	111	156
9	111	167
17	111	167
16	111	178
8	111	178
18	111	196
10	111	196
15	111	198
7	111	198
21	111	223
13	111	223

account_id	identifier	count
7	14	2
111	150	2
111	155	2
111	156	3
111	167	2
111	178	2
111	196	2
111	198	2
111	223	2

id	account_id	identifier	id	new_identifier
3	7	16	3	16
11	111	224	11	224
14	111	225	14	225
15	111	226	15	226
16	111	227	16	227
17	111	228	17	228
18	111	229	18	229
19	111	230	19	230
20	111	231	20	231
21	111	232	21	232

account_id	identifier	count
7	13	1
7	14	1
7	15	1
7	16	1
111	150	1
111	155	1
111	156	1
111	167	1
111	178	1
111	196	1
111	198	1
111	223	1
111	224	1
111	225	1
111	226	1
111	227	1
111	228	1
111	229	1
111	230	1
111	231	1
111	232	1

id	account_id	identifier
1	7	13
2	7	14
4	7	15
3	7	16
12	111	150
6	111	155
5	111	156
9	111	167
8	111	178
10	111	196
7	111	198
13	111	223
11	111	224
14	111	225
15	111	226
16	111	227
17	111	228
18	111	229
19	111	230
20	111	231
21	111	232

id	account_id	identifier
1	7	13
2	7	14
3	7	14
4	7	15
12	111	150
20	111	150
14	111	155
6	111	155
11	111	156
5	111	156
19	111	156
9	111	167
17	111	167
16	111	178
8	111	178
18	111	196
10	111	196
15	111	198
7	111	198
21	111	223
13	111	223

account_id	identifier	count
7	14	2
111	150	2
111	155	2
111	156	3
111	167	2
111	178	2
111	196	2
111	198	2
111	223	2

id	account_id	identifier	id	new_identifier
3	7	16	3	16
11	111	224	11	224
14	111	225	14	225
15	111	226	15	226
16	111	227	16	227
17	111	228	17	228
18	111	229	18	229
19	111	230	19	230
20	111	231	20	231
21	111	232	21	232

account_id	identifier	count
7	13	1
7	14	1
7	15	1
7	16	1
111	150	1
111	155	1
111	156	1
111	167	1
111	178	1
111	196	1
111	198	1
111	223	1
111	224	1
111	225	1
111	226	1
111	227	1
111	228	1
111	229	1
111	230	1
111	231	1
111	232	1

id	account_id	identifier
1	7	13
2	7	14
4	7	15
3	7	16
12	111	150
6	111	155
5	111	156
9	111	167
8	111	178
10	111	196
7	111	198
13	111	223
11	111	224
14	111	225
15	111	226
16	111	227
17	111	228
18	111	229
19	111	230
20	111	231
21	111	232