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

Ironstone

Answer 1

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.

Italia

This is the way to do it. In a multi-user environment it is very hard to enforce gapless numbering without creating race conditions. You would have to use table locks heavily, which is a real downer for performance. I suggest you use row_number() instead of rank(). Result is the same in the absence of peers (like in this case), but row_number() is the cheaper and more appropriate function. – Draggletailed

@ErwinBrandstetter Thank you for row_number(). I edited the queries. – Italia

Can I later change the order of the tasks inside a month? – Ironstone

@Alec Yes. You will need to change the value in the serial_column. – Italia

I am a little lost, which will be the serial_column? The month or the task? – Ironstone

@Alec If you want to define an order on the tasks in the month - you need additional column in the table itself. In my example I added column with name serial_column and type SERIAL(= auto increment). – Italia

@Alec Wait a minute. I will add an explanation. – Italia

@Alec Added detailed explanation. – Italia

Thanks Igor, now it makes perfect sense and I actually find it a really neat solution. – Ironstone

Hey there, is there a way to group the month and the row_number to a unique contraint? i want to have something like a version of a document which should have the version and the name of the document as a unique contraint and the version should just be a number that gets raised by 1 everytime the document gets updated – Beutner

Answer 2

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

Victorvictoria