在PostgreSQL中递增多列序列

Question

是否有任何内置的方式（我的意思是，不需要触发器和/或函数）来对多个列进行递增索引？

因此，在执行以下操作后：

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')

我的表格最终会变成这样（请注意“任务”列）：

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

- user1598585

Answer 1

你可以向你的表格添加一个简单的SERIAL列（它将为你的事物提供顺序），然后使用类似以下的内容：

您可以向表格添加一个简单的SERIAL列（它将为事物提供顺序），然后使用类似以下内容：

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

这将会给您想要的结果。

如果您不需要对行进行排序，可以尝试：

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

详情请见：row_number() OVER(...)

更新：它的工作原理：

具有类型SERIAL的列本质上是一个“自动递增”字段。它会自动从序列中获取一个值。当您向表中插入行时，它们将如下所示：

| 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 |

关键是：每一行的SERIAL_COLUMN都比前面所有行的值大。

接下来，row_number() OVER (PARTITION BY month ORDER BY serial_column)所做的是：

1）将所有行根据相同的month分成组（PARTITION BY month）

2）按照serial_column的值对它们进行排序（ORDER BY serial_column）

3）使用步骤2中的顺序为每个组分配一个行号（`row_number() OVER）

输出结果为：

| 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 |

要更改row_number()的输出，您需要更改SERIAL_COLUMN中的值。例如，要将第二个月份的第二和最后一个放在第二个月份的第一个之前，需要像这样更改SERIAL_COLUMN的值：

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';

它将改变查询的输出：

| 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 |

SERIAL_COLUMN 的确切值并不重要。 它们只是为一个月中的任务设置顺序。

我的 SQLFiddle 示例在此处。

- Ihor Romanchenko

---

@ErwinBrandstetter 感谢您提供 row_number()。我已经编辑了查询。 - Ihor Romanchenko

我后来可以更改一个月内任务的顺序吗？ - user1598585

@Alec 添加了详细的解释。 - Ihor Romanchenko

谢谢Igor，现在我完全明白了，而且我觉得这是一个非常棒的解决方案。 - user1598585

嗨，有没有一种方法可以将月份和行号分组到唯一的约束条件中？我想要一个文档的版本，它应该具有文档的版本和名称作为唯一的约束条件，而版本应该只是一个数字，每次更新文档时都会增加1。 - Christian Schmitt

Answer 2

如果您愿意将您的INSERT语句拆分成每个插入一行数据，那么您可以使用PostgreSQL规则。下面的示例有些复杂，因为规则似乎不允许您将写操作重定向到关系本身。这通常是由触发器完成的。但我们正在测试是否无需触发器就可以实现这一点。

--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";

注意事项

• 如果您需要支持在“table”上进行DELETE/UPDATE操作，则可以为每个操作添加规则。
• 上面的BEGIN和COMMIT块用于显示，即使在同一事务中，只要将每行分解为自己的INSERT，此方法也将起作用。
• 您使用了一些保留字，例如table和desc。确保像您所做的那样将它们用双引号括起来，就不会有任何问题。

这里是上述代码在sqlfiddle中的演示

- Joshua Berry