Alter a column data type in all tables

We must change the timestamp format for all columns of type timestamp.

The format is like that :

2017-06-14 17:47:23.295343

and it must be like that :

2017-06-14 17:47:23

The SQL command to be done is :

alter table alter column my_timestamp_column type timestamp 0;

And we embed this command into loop that takes relevant information from the catalog :

create or replace function public.alter_all_timestamps() returns void as $$

declare
  r       record;
  stmt    varchar;
begin
  for r in 
    select 
        c.table_name
       ,c.column_name
    from 
       information_schema.columns as c
    inner join 
        information_schema.tables as t 
    on 
        c.table_name = t.table_name
    where 
        c.table_schema = 'public'
    and 
        c.data_type = 'timestamp without time zone'
    and 
        t.table_type = 'BASE TABLE'
  loop
    
        stmt = 'alter table ' 
                || quote_ident(r.table_name )
                || ' alter column '
                || quote_ident(r.column_name)
                || ' type timestamp(0)';
                
        execute stmt;

    end loop;

end $$ language 'plpgsql' volatile;

That’s simple !

If there are views on those tables that select a column of type timestamp, those views must be dropped before the run of the function and recreated after.

By the way, when I was writing this article, I was listening Nagoya Guitars of Steve Reich.