Postgresql - increment counter in rows where a column has duplicate value

Question

I have added a column (seq) to a table used for scheduling so the front end can manage the order in which each item can be displayed. Is it possible to craft a SQL query to populate this column with an incremental counter based on the common duplicate values in the date column?

Before

------------------------------------
| name | date_time           | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 |     |
| ABC2 | 16-01-2017 11:30:00 |     |
| ABC1 | 16-01-2017 11:30:00 |     |
| ABC3 | 17-01-2017 10:00:00 |     |
| ABC3 | 18-01-2017 12:30:00 |     |
| ABC4 | 18-01-2017 12:30:00 |     |
| ABC1 | 18-01-2017 12:30:00 |     |
------------------------------------

After

------------------------------------
| name | date_time           | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 | 0   |
| ABC2 | 16-01-2017 11:30:00 | 0   |
| ABC1 | 16-01-2017 11:30:00 | 1   |
| ABC3 | 17-01-2017 10:00:00 | 0   |
| ABC3 | 18-01-2017 12:30:00 | 0   |
| ABC4 | 18-01-2017 12:30:00 | 1   |
| ABC1 | 18-01-2017 12:30:00 | 2   |
------------------------------------

Solved, thanks to both answers. To make it easier for anybody who finds this, the working code is:

UPDATE my_table f
    SET seq = seq2
    FROM (
        SELECT ctid, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY ctid) -1 AS seq2
        FROM my_table
        ) s
    WHERE f.ctid = s.ctid;

klin · Accepted Answer · 2019-06-03T14:00:09.710

4

Use the window function row_number():

with my_table (name, date_time) as (
values

('ABC1', '15-01-2017 11:00:00'),
('ABC2', '16-01-2017 11:30:00'),
('ABC1', '16-01-2017 11:30:00'),
('ABC3', '17-01-2017 10:00:00'),
('ABC3', '18-01-2017 12:30:00'),
('ABC4', '18-01-2017 12:30:00'),
('ABC1', '18-01-2017 12:30:00')
)

select *, 
    row_number() over (partition by name order by date_time)- 1 as seq
from my_table
order by date_time;

 name |      date_time      | seq 
------+---------------------+-----
 ABC1 | 15-01-2017 11:00:00 |   0
 ABC1 | 16-01-2017 11:30:00 |   1
 ABC2 | 16-01-2017 11:30:00 |   0
 ABC3 | 17-01-2017 10:00:00 |   0
 ABC1 | 18-01-2017 12:30:00 |   2
 ABC3 | 18-01-2017 12:30:00 |   1
 ABC4 | 18-01-2017 12:30:00 |   0
(7 rows)

Read this answer for a similar question about updating existing records with a unique integer.

edited Jun 03 '19 at 14:00

answered Oct 11 '17 at 23:08

klin

112,967
15
204
232

This provides the numbers, how do I update the table with these numbers? – corky Oct 11 '17 at 23:18
How could I increment counter when the values DON'T duplicate (i.e., the opposite case) ? – igorkf Sep 14 '20 at 18:30
@igorkf - please ask a new question with sample data and expected output. – klin Sep 14 '20 at 19:30

zambonee · Answer 2 · 2017-10-11T23:53:33.010

3

Check out ROW_NUMBER().

SELECT name, date_time, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY name) FROM [table]

edited Oct 11 '17 at 23:53

answered Oct 11 '17 at 23:01

zambonee

1,599
11
17

That displays the numbers very nicely but it does not populate the new column. – corky Oct 11 '17 at 23:10
This returns the following error: ERROR: relation "t" does not exist LINE 1: UPDATE t set seq = seq2 FROM ( – corky Oct 11 '17 at 23:48
Turns out my UPDATE statement does not work in PostGres (but it does work in SQL Server). Your table needs a primary key to update, or you can do something like https://stackoverflow.com/a/6578705/1933308 – zambonee Oct 11 '17 at 23:58
thanks, I used ctid as per the link that klin provided – corky Oct 12 '17 at 00:18

Postgresql - increment counter in rows where a column has duplicate value

2 Answers2