CODEWITHSUNDEEP
postgresqldatabase-migrationpostgresql-10sqitch
nackjicholson
nackjicholson

Reputation: 4839

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

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.

Upvotes: 2

Views: 755

Answers (2)

theory
theory

Reputation: 9887

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

Upvotes: 3

nackjicholson
nackjicholson

Reputation: 4839

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.

Upvotes: 1

Related Questions