How can I verify an alter column data type change with sqitch postgresql?

Question

I am making the following deploy change. Changing the value of a timestamp column to 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';

How can I write a verify script that will error if the data type has not been changed?

Also need some help with revert to drop timezone from a timestamptz.

Answer 1

Your solution looks pretty good to me, @nackjicholson. If you wanted a more informative error message, you could wrap it in a DO block and raise an error:

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