问Postgresql Alter Column以幂等方式

EN

drop table if exists my_table;

create table my_table (my_column int);

-- vvvvv alter column idempotently vvvvv
do $$
begin
    
if not exists (
    select 1
    from information_schema."columns"
    where table_name               = 'my_table'
      and column_name              = 'my_column'
      and data_type                = 'character varying'
      and character_maximum_length = 50)
then
    alter table my_table alter column my_column type varchar(50);
    raise notice 'altered the column definition';
else
    raise notice 'data type is already correct';
end if;

end $$;
-- ^^^^^ alter column idempotently ^^^^^

-- Run that same block of code again...
do $$
begin
    
if not exists (
    select 1
    from information_schema."columns"
    where table_name               = 'my_table'
      and column_name              = 'my_column'
      and data_type                = 'character varying'
      and character_maximum_length = 50)
then
    alter table my_table alter column my_column type varchar(50);
    raise notice 'altered the column definition';
else
    raise notice 'data type is already correct';
end if;

end $$;

altered the column definition
data type is already correct