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 my_table 
   alter column my_timestamp_column type timestamp(0);

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

create or replace function public.alter_all_timestamps() 
 returns void as $$
  r       record;
  stmt    varchar;
  for r in 
        information_schema.columns as c
    inner join 
        information_schema.tables as t 
        c.table_name = t.table_name
        c.table_schema = 'public'
        c.data_type = 'timestamp without time zone'
        t.table_type = 'BASE TABLE'
        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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s