Is there any built-in way (I mean, without need of triggers and/or functions) to have incrementing indexes per multiple columns?
So after performing:
INSERT INTO "table"
("month", "desc")
VALUES
(1, 'One thing')
, (1, 'Another thing')
, (1, 'Last task of the month')
, (2, 'Last task of the month')
, (2, 'Last task of the month')
, (3, 'First of third month')
My table would end up like this (note the "task" column):
month task desc
1 1 One thing
1 2 Another thing
1 3 Last task of the month
2 1 First of second month
2 2 Second and last of second month
3 1 First of third month
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.