Incrementing multi-column sequence in PostgreSQL

Question

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

Answer 1

If you are willing to break your INSERT statements into one row of data per insert, then you could use PostgreSQL rules. The example that follows is a bit convoluted in that rules don't seem to let you redirect writes to a relation itself. That's usually done with triggers. But we're seeing if this is possible without triggers, so here goes:

--drop table table_data cascade;
CREATE TABLE table_data (
  month integer not null,
  task integer not null,
  "desc" text
);
ALTER TABLE table_data add primary key (month, task);

CREATE VIEW "table" as 
 select month, task, "desc" from table_data;

CREATE OR REPLACE RULE calculate_task AS ON INSERT TO "table"
  DO INSTEAD
  INSERT into table_data (month, task, "desc") 
  VALUES (
  NEW.month, 
  (select coalesce(max(task),0) + 1 from table_data where month = NEW.month), 
  NEW."desc");

BEGIN;
INSERT INTO "table" ("month", "desc") VALUES (1, 'One thing');
INSERT INTO "table" ("month", "desc") VALUES (1, 'Another thing');
INSERT INTO "table" ("month", "desc") VALUES (1, 'Last task of the month');
INSERT INTO "table" ("month", "desc") VALUES (2, 'Last task of the month');
INSERT INTO "table" ("month", "desc") VALUES (2, 'Last task of the month');
INSERT INTO "table" ("month", "desc") VALUES (3, 'First of third month');
COMMIT;

select * from "table";

Notes

• If you need to support DELETE/UPDATE on "table", then you could add rules for each those actions as well.
• The BEGIN and COMMIT block above is used to show that even within the same transaction, this method will work as long as each row is broken into its own INSERT.
• You use a few reserved words like table and desc. Be sure to double-quote them as you've done and you won't have any problems.

Here is the above code in sqlfiddle

Answer 2

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.