Incrementing multi-column sequence in PostgreSQL

multiple-columns, postgresql, sequences

Solution

You can add simlpe `SERIAL` column to your table (it will give you the order for things) and then use something like:

SELECT *, row_number() OVER (PARTITION BY month ORDER BY serial_column)
FROM table

This will give you the results you want.

If you do not need to order the rows, you can try:

SELECT *, row_number() OVER (PARTITION BY month)
FROM table

Details here : `row_number() OVER(...)`

UPD How it works:

A column with type `SERIAL` is essentially an "auto increment" field. It automatically get a value from a sequence. When you insert rows to the table they will look like this:

| MONTH | SERIAL_COLUMN |                     DESCRIPTION |
-----------------------------------------------------------
|     1 |             1 |                       One thing |
|     1 |             2 |                   Another thing |
|     1 |             3 |          Last task of the month |
|     2 |             4 |           First of second month |
|     2 |             5 | Second and last of second month |
|     3 |             6 |            First of third month |

The key thing - every next added row has value of `SERIAL_COLUMN` greater than all previous rows.

Next. The `row_number() OVER (PARTITION BY month ORDER BY serial_column)` does:

1) Partition all the rows into groups with equal `month` (`PARTITION BY month`)

2) Orders them by value of `serial_column` (`ORDER BY serial_column`)

3) In every group assigns a row number using the ordering from step 2 (``row_number() OVER`)

The output is:

| MONTH | SERIAL_COLUMN |                     DESCRIPTION | ROW_NUMBER |
------------------------------------------------------------------------
|     1 |             1 |                       One thing |          1 |
|     1 |             2 |                   Another thing |          2 |
|     1 |             3 |          Last task of the month |          3 |
|     2 |             4 |           First of second month |          1 |
|     2 |             5 | Second and last of second month |          2 |
|     3 |             6 |            First of third month |          1 |

To change the output of the `row_number()` you need to change the values in `SERIAL_COLUMN`. Fro example, to place `Second and last of second month` before `First of second month` a will change the values of `SERIAL_COLUMN` like that:

UPDATE Table1
SET serial_column = 5
WHERE description = 'First of second month';

UPDATE Table1
SET serial_column = 4
WHERE description = 'Second and last of second month';

It will change the output of the query:

| MONTH | SERIAL_COLUMN |                     DESCRIPTION | ROW_NUMBER |
------------------------------------------------------------------------
|     1 |             1 |                       One thing |          1 |
|     1 |             2 |                   Another thing |          2 |
|     1 |             3 |          Last task of the month |          3 |
|     2 |             4 | Second and last of second month |          1 |
|     2 |             5 |           First of second month |          2 |
|     3 |             6 |            First of third month |          1 |

The exact values in `SERIAL_COLUMN` do not matter. They only set an order on the tasks in a month.

My SQLFiddle example is here.

Problem

Original source