主页 / coding / 问题 / 79574708
Accepted
SomeGuy
Asked: 2025-04-15 16:40:56 +0800 CST

在计算列中使用带有时区的时间戳

  • 772

我需要连续迁移两个非常大的表。我的第一个解决方案是逐列比较以找出哪些发生了更改，但这样做成本太高。

所以第二个想法是创建持久化计算列来存储集合列的散列值。这可以加快处理速度。但问题是，使用带时区的时间戳对列进行散列时出现了问题。我收到了这个错误：ERROR: generation expression is not immutable。

我尝试了各种方法来转换值，但都不起作用。

代码（注释值，无效）：

ALTER TABLE schema.table ADD COLUMN hashed_columns UUID GENERATED ALWAYS AS (
    md5(
        id::text ||
        type_id::text ||
        --EXTRACT(EPOCH FROM date)::text ||
        -- to_char(date::timestamp without time zone , 'YYYY-MM-DD"T"HH24:MI:SS') ||
        -- date::text ||
        -- to_char(date, 'YYYYMMDD')::integer ||
        -- md5(date::text) ||
        to_char(date, 'YYYYMMDD') ||
        value1::text ||
        value2::text
    )::uuid
) STORED;

看起来，它检测到了不可变列，然后直接抛出错误，而没有先尝试强制类型转换。我尝试做的事情从根本上来说错了吗？还是说有可能？

当我从脚本中删除日期列时，它可以工作，所以问题出在时间戳内。

服务器正在运行 Postgre 16。

postgresql

2 个回答

  1. Best Answer

    如果你想要一个IMMUTABLE表达式将 a 转换timestamp with time zone为可以连接的内容，请使用

    extract(epoch FROM date AT TIME ZONE 'UTC')

    我认为命名时间戳列并不是一个好主意date，但这只是一个题外话。

    对于你最初的问题：与其计算所有列的哈希值或比较所有列，为什么不直接添加一个列，version该列要么填充序列值，要么填充每个列的当前时间戳UPDATE？这样，你就可以只比较这一列来确定该行是否被更改了。这比计算哈希值便宜得多。

    • 1

    1. 如果我理解正确，我会大胆地建议这种方法。在表中添加一个新列（布尔值即可）。插入新行（更改现有行）时，在该列写入 False。传输完成后，赋值 True。为了识别更改的数据，会执行单列搜索。

    2. 如果您不喜欢这种方法或对此感到不舒服，我会回答您的问题。我们需要使您的列不可变。您可以将其放入一个单独的函数中来实现，该函数的参数将是需要处理的字段：

    ALTER TABLE schema.table ADD COLUMN hashed_columns UUID GENERATED ALWAYS AS (
get_hash(parameter1, parameter2, parameter3, ... parameterN)
) STORED;

    将所有计算放入函数本身并在列中得到结果：

    CREATE OR REPLACE FUNCTION schema.get_hash(
    parameter1, parameter2, parameter3, ... parameterN)
    RETURNS uuid
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN (
      md5(
        id::text ||
        type_id::text ||
        EXTRACT(EPOCH FROM date)::text ||
         to_char(date::timestamp without time zone , 'YYYY-MM-DD"T"HH24:MI:SS') ||
         date::text ||
         to_char(date, 'YYYYMMDD')::integer ||
         md5(date::text) ||
        to_char(date, 'YYYYMMDD') ||
        value1::text ||
        value2::text
      )::uuid
    );
END;
$BODY$;

    如果存在任何句法缺陷，修复它们并不困难。

    我做了一个简单的例子（只有一个参数，逻辑保留）：

    CREATE OR REPLACE FUNCTION mis.get_hashX(
    dateX timestamp)
    RETURNS uuid
    LANGUAGE 'plpgsql'
    IMMUTABLE PARALLEL SAFE
AS $BODY$
BEGIN
    RETURN (
      md5(
        EXTRACT(EPOCH FROM dateX)::text ||
         to_char(dateX::timestamp without time zone , 'YYYY-MM-DD"T"HH24:MI:SS')
      )::uuid
    );
END;
$BODY$;

    SELECT mis.get_hashX('01-01-2025 12:00:00');

    在此处输入图片描述

    • 0

相关问题