We must change the timestamp format for all columns of type timestamp.
The format is like that :
and it must be like that :
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 $$ 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.