postgresql - 如何使用 sqitch postgresql 验证更改列数据类型的更改？

Question

我正在进行以下 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';

如果数据类型未更改，我如何编写一个验证脚本会出错？

还需要一些关于 revert 的帮助以从 timestamptz 中删除时区。

Answer 1

@nackjicholson，你的解决方案对我来说看起来很不错。如果您想要一个信息更丰富的错误消息，您可以将其包装在 DO block 中并引发错误:

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;
$$;