PostgreSQL技术需求：对timestamptz列执行INSERT/UPDATE时强制要求显式指定时区

这确实是个非常务实的需求——时区歧义绝对是后期排查问题的噩梦，能在数据库层面把这个坑堵上太有必要了。你提到的几个方案里，触发器思路方向是对的，但确实存在“无法获取原始输入字符串”的痛点，我来给你梳理几个可行的实现方案，以及各自的优劣：

方案1：INSTEAD OF 视图触发器（推荐，最贴合需求） #

这个方案通过视图拦截所有写入操作，强制用户输入带时区的字符串，验证通过后再转换为timestamptz存入原始表，完美解决“解析后无法判断原始输入”的问题：

1. 创建原始表（用timestamptz类型存储，保留所有原生支持）：

CREATE TABLE your_target_table (
    id serial PRIMARY KEY,
    event_time timestamptz NOT NULL,
    -- 其他列...
);

2. 创建操作视图（用text类型接收日期输入，确保我们能拿到原始字符串）：

CREATE VIEW your_target_table_view AS
SELECT id, event_time::text AS event_time
FROM your_target_table;

3. 编写验证触发器函数：

CREATE OR REPLACE FUNCTION enforce_tz_input()
RETURNS TRIGGER AS $$
DECLARE
    -- 这里替换成你已经写好的正则表达式，确保匹配带时区的日期字符串
    tz_pattern text := '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[+-]\d{2}(:\d{2})?$';
BEGIN
    -- 验证输入的日期字符串是否带时区
    IF NEW.event_time !~ tz_pattern THEN
        RAISE EXCEPTION 'Column "event_time" must include an explicit time zone (e.g., ''2024-05-20 14:30:00+08'')';
    END IF;

    -- 根据操作类型写入原始表
    IF TG_OP = 'INSERT' THEN
        INSERT INTO your_target_table (event_time)
        VALUES (NEW.event_time::timestamptz)
        RETURNING id INTO NEW.id;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE your_target_table
        SET event_time = NEW.event_time::timestamptz
        WHERE id = NEW.id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql STRICT;

4. 绑定触发器到视图：

CREATE TRIGGER validate_tz_on_write
INSTEAD OF INSERT OR UPDATE ON your_target_table_view
FOR EACH ROW EXECUTE FUNCTION enforce_tz_input();

5. 权限控制：
   撤销用户对原始表的直接操作权限，只开放视图的读写权限：

REVOKE ALL ON your_target_table FROM app_user;
GRANT SELECT, INSERT, UPDATE ON your_target_table_view TO app_user;

优势：完全拦截不带时区的原始输入，保留timestamptz的所有原生运算符和函数支持，开发人员只需操作视图即可，学习成本低。

方案2：自定义验证函数+存储过程（适合严格权限场景） #

如果你的团队习惯用存储过程操作数据库，可以通过强制调用验证函数来实现：

1. 创建带验证的转换函数：

CREATE OR REPLACE FUNCTION timestamptz_with_tz(input_str text)
RETURNS timestamptz AS $$
DECLARE
    tz_pattern text := '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[+-]\d{2}(:\d{2})?$';
BEGIN
    IF input_str !~ tz_pattern THEN
        RAISE EXCEPTION 'Input must include explicit time zone';
    END IF;
    RETURN input_str::timestamptz;
END;
$$ LANGUAGE plpgsql STRICT;

2. 创建封装的存储过程：

CREATE OR REPLACE FUNCTION insert_event(p_event_time text)
RETURNS void AS $$
BEGIN
    INSERT INTO your_target_table (event_time)
    VALUES (timestamptz_with_tz(p_event_time));
END;
$$ LANGUAGE plpgsql;

-- 同理编写UPDATE的存储过程

3. 权限控制：
   只授予用户存储过程的执行权限，禁止直接操作表：

REVOKE INSERT, UPDATE ON your_target_table FROM app_user;
GRANT EXECUTE ON FUNCTION insert_event(text) TO app_user;

优势：完全控制数据写入路径，适合对权限要求极高的场景；劣势是开发人员必须通过存储过程操作，灵活性稍差。

关于你最初考虑的触发器方案 #

你提到的BEFORE INSERT/UPDATE触发器遍历列的思路，代码上是可以实现的，但核心问题是：触发器拿到的NEW记录里的timestamptz值已经是PostgreSQL解析后的结果（哪怕原始输入不带时区，也会被自动加上会话时区），无法反向判断原始输入是否显式指定了时区。所以这个方案无法满足你的核心需求，不建议采用。

比如遍历列的代码示例（仅作技术参考，不能解决你的问题）：

CREATE OR REPLACE FUNCTION check_tz_columns()
RETURNS TRIGGER AS $$
DECLARE
    col RECORD;
    col_value timestamptz;
BEGIN
    FOR col IN
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = TG_TABLE_NAME
          AND table_schema = TG_TABLE_SCHEMA
          AND data_type = 'timestamp with time zone'
    LOOP
        -- 获取列值，但此时已经是解析后的timestamptz
        EXECUTE format('SELECT $1.%I', col.column_name) INTO col_value USING NEW;
        -- 这里无法判断原始输入是否带时区
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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