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.

theory · Accepted Answer · 2023-08-16T16:47:38.500

3

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

edited Aug 16 '23 at 16:47

answered Aug 21 '18 at 15:03

theory

9,178
10
59
129

1

Thanks for the help David! – nackjicholson Aug 21 '18 at 15:40

score 1 · Answer 2 · answered Aug 20 '18 at 20:11

SELECT 1/count(*)
FROM   information_schema.columns
WHERE  table_name = 'sources'
 and column_name = 'is_deleted'
 and data_type = 'timestamp with time zone';

That's what I can come up with, but not being an expert I'm not sure if that's the way to do it or just stupid. :)

This throws a division by zero error if no rows found with that criteria.

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

2 Answers2