我该如何在PostgreSQL中根据行创建日期创建自定义序列？

Question

我正在为雇主更换传统的订单管理应用程序。新系统的规格之一是订单编号系统保持不变。目前，我们的订单号格式如下：

• 前四位数字是当前年份
• 接下来的两位数字是当前月份
• 最后四位数字是一个计数器，每次在该月份放置订单时递增。

例如，2014年6月放置的第一个订单将具有订单号2014060001。下一个下单的订单将具有订单号2014060002，以此类推。

这个订单号需要成为订单表中的主要ID。似乎我需要为PostgreSQL设置一个自定义序列来分配主键，但是我能找到的关于创建自定义序列的唯一文档非常基础（如何以二而不是一递增等）。

如何根据上述日期创建自定义序列？

- rockusbacchus

---

对于此操作，您不能使用序列，因为序列可能会在交易失败时导致数字间的间隔，而且序列不支持回滚。 - Frank Heikens

我一直使用序列作为主键，但很少情况下会发生主键值跳跃几百或几千的情况。 - abhishek77in

@FrankHeikens 我认为有更好的方法：https://dev59.com/K4Dba4cB1Zd3GeqPE3wc#69546178 - Evan Carroll

Answer 1

您可以使用EXTRACT()函数将手动创建的序列设置为特定值：

setval('my_sequence',
  (EXTRACT(YEAR FROM now())::integer * 1000000) +
  (EXTRACT(MONTH FROM now())::integer * 10000)
);

下一个输入的订单将采用序列中的下一个值，即YYYYMM0001等。

技巧在于何时更新序列值。您可以在PG内部以困难的方式完成，并在订单表上编写BEFORE INSERT触发器，检查这是否是新月份中的第一条记录。

CREATE FUNCTION before_insert_order() RETURNS trigger AS $$
DECLARE
  base_val  integer;
BEGIN
  -- base_val is the minimal value of the sequence for the current month: YYYYMM0000
  base_val := (EXTRACT(YEAR FROM now())::integer * 1000000) +
              (EXTRACT(MONTH FROM now())::integer * 10000);

  -- So if the sequence is less, then update it
  IF (currval('my_sequence') < base_val)
    setval('my_sequence', base_val);
  END IF;

  -- Now assign the order id and continue with the insert
  NEW.id := nextval('my_sequence');
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_bi_order
  BEFORE INSERT ON order_table
  FOR EACH ROW EXECUTE PROCEDURE before_insert_order();

为什么这是一种困难的方法呢？因为您需要在每次插入时检查序列的值。如果您每天只插入几条记录，并且您的系统不是非常繁忙，那么这是一种可行的方法。

如果您不能抽出所有这些CPU周期，您可以安排一个工作，在每个月的第一天的00:00:01运行一个PG函数，通过<代码>psql 更新序列，然后只需将序列用作新订单记录的默认值（因此无需触发器）。

- Patrick

Answer 2

另一个我更喜欢的想法是这样的：

1. 创建一个函数，从时间戳和发票号生成您的id，
2. 创建一个常规表格，其中包含：
   • foo_id: 简单的序列（递增整数）
   • ts_created字段。
3. 在需要时查询生成您的发票id，

下面是它的样子，首先我们创建一个函数，从bigint和timestamp生成acme_id。

CREATE FUNCTION acme_id( seqint bigint, seqts timestamp with time zone )
RETURNS char(10)
AS $$
  SELECT format(
    '%04s%02s%04s',
    EXTRACT(year FROM seqts),
    EXTRACT(month from seqts),
    to_char(seqint, 'fm0000')
  );
$$ LANGUAGE SQL
IMMUTABLE;

然后我们创建一个表格。

CREATE TABLE foo (
  foo_id        int  PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  data          text,
  ts_created    timestamp with time zone DEFAULT NOW()
);
CREATE INDEX ON foo(ts_created, foo_id);

现在，您可以使用简单的窗函数生成您想要的内容。

SELECT acme_id(
  ROW_NUMBER() OVER (
    PARTITION BY date_trunc('MONTH', ts_created)
    ORDER BY ts_created
  ),
  ts_created
), *
FROM foo;

我会构建系统，使其在内部使用 foo_id。只要您不从 foo 中删除，就始终能够从行中呈现相同的发票 ID，只是无需存储它。

甚至可以使用 [materialized] 视图缓存呈现和发票 ID。

CREATE MATERIALIZED VIEW acme_invoice_view
AS
    SELECT acme_id(
      ROW_NUMBER() OVER (
        PARTITION BY date_trunc('MONTH', ts_created)
        ORDER BY ts_created
      ),
      ts_created
    ), *
    FROM foo;
;

SELECT * FROM acme_invoice_view;
  acme_id   | foo_id | insert_date | data 
------------+--------+-------------+------
 2021100001 |      1 | 2021-10-12  | bar
(1 row)

请记住此方法的缺点：

• 发票表中的行永远不能被删除（你可以添加一个布尔值来停用它们），
• foo_id和ts_created应该是不可变的（永远不会更新），否则可能会得到一个新的发票ID。代理键（foo_id无论如何都不应该更改）。

这种方法的好处：

• 存储发票非常有用的实际时间戳
• 真正的代理键（我会在所有上下文中使用它，而不是发票ID），简化了与其他表的链接，并且更有效率和快速。
• 发票日期的单一真相来源
• 很容易发出新的发票-id方案，甚至将其映射到旧方案中。

- Evan Carroll