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

verify如果数据类型未更改，如何编写会出错的脚本？

还需要一些帮助revert来从时间戳记中删除时区。

标签： postgresqldatabase-migrationpostgresql-10sqitch

Answer 1

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