PostgreSQL生成列提示表达式非不可变，求不可变整数转文本函数

我在给tag表添加字段时，希望创建一个用于唯一索引的生成列来拼接多个字段，但执行以下SQL时报错：ERROR: generation expression is not immutable。

我已参考方案使用CASE和||进行字符串拼接（二者应为immutable），SQL语句如下：

ALTER TABLE tag
  ADD COLUMN prefix VARCHAR(4) NOT NULL,
  ADD COLUMN middle BIGINT NOT NULL,
  ADD COLUMN postfix VARCHAR(4), -- nullable
  -- VARCHAR size is 4 prefix + 19 middle + 4 postfix + 2 delimiter
  ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
    (CASE WHEN postfix IS NULL THEN prefix || '-' || middle
          ELSE prefix || '-' || middle || '-' || postfix
          END
    ) STORED;
CREATE UNIQUE INDEX unq_tag_tag_id ON tag(tag_id);

PostgreSQL邮件列表中提到：

integer-to-text coercion, [...] isn't necessarily immutable

但未给出对应的不可变整数转文本函数，请问是否存在这样的函数？

问题核心在于bigint类型转字符串的隐式转换操作不具备immutable属性——PostgreSQL中bigint::text的底层转换逻辑受区域设置等因素影响，因此无法被认定为immutable，导致生成列表达式不符合要求。

你可以通过以下两种方式解决：

• 使用内置的to_char函数（该函数为immutable）显式转换bigint为字符串：

  ALTER TABLE tag
    ADD COLUMN prefix VARCHAR(4) NOT NULL,
    ADD COLUMN middle BIGINT NOT NULL,
    ADD COLUMN postfix VARCHAR(4), -- nullable
    -- VARCHAR size is 4 prefix + 19 middle + 4 postfix + 2 delimiter
    ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
      (CASE WHEN postfix IS NULL THEN prefix || '-' || to_char(middle, 'FM9999999999999999999')
            ELSE prefix || '-' || to_char(middle, 'FM9999999999999999999') || '-' || postfix
            END
      ) STORED;
  CREATE UNIQUE INDEX unq_tag_tag_id ON tag(tag_id);
  

  其中FM修饰符用于移除数字前的空格，确保转换结果和隐式转换完全一致。

• 自定义一个immutable的bigint转字符串函数：

  CREATE OR REPLACE FUNCTION bigint_to_text(bigint)
  RETURNS text
  LANGUAGE sql
  IMMUTABLE
  AS $$
  SELECT textin(int8out($1));
  $$;
  

  之后在生成列中调用该函数：

  ALTER TABLE tag
    ADD COLUMN prefix VARCHAR(4) NOT NULL,
    ADD COLUMN middle BIGINT NOT NULL,
    ADD COLUMN postfix VARCHAR(4), -- nullable
    ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
      (CASE WHEN postfix IS NULL THEN prefix || '-' || bigint_to_text(middle)
            ELSE prefix || '-' || bigint_to_text(middle) || '-' || postfix
            END
      ) STORED;
  CREATE UNIQUE INDEX unq_tag_tag_id ON tag(tag_id);
  

内容的提问来源于stack exchange，提问作者Kevin