我正在进行以下deploy
更改。将timestamp
列的值更改为timestamptz
。
-- Alter the is_deleted flags to be timestamp with time zone
alter table source_meta.sources alter column is_deleted set data type timestamptz
using
is_deleted at time zone 'UTC';
alter table source_meta.series alter column is_deleted set data type timestamptz
using
is_deleted at time zone 'UTC';
如何编写一个在数据类型未更改时出错的verify
脚本?
还需要一些帮助,revert
从时间戳中删除时区。
@nackjicholson,您的解决方案对我来说看起来不错。如果你想要一个信息更丰富的错误消息,你可以把它包装在一个DO
块中并引发一个错误:
DO $$
BEGIN
PERFORM TRUE
FROM information_schema.columns
WHERE table_name = 'sources'
AND column_name = 'is_deleted'
AND data_type = 'timestamp with time zone';
IF NOT FOUND THEN
RAISE EXCEPTION 'sources.is_deleted type is not timestamptz';
END IF;
END;
$$;
SELECT 1/count(*)
FROM information_schema.columns
WHERE table_name = 'sources'
and column_name = 'is_deleted'
and data_type = 'timestamp with time zone';
这就是我能想到的,但不是专家,我不确定这是这样做的方法还是愚蠢的。 :)
如果未找到符合该条件的行,则会引发除以零错误。