Search code examples
sqlpostgresqltypestimestampdatabase-migration

postgres bulk data type change

I needed to change all tables columns in the whole DB from data type timestampwithouttime zone to timestamp(0)withtime zone.
So I created this function: 

CREATE OR REPLACE FUNCTION grd_replace_datetime_with_timezone()
  RETURNS character varying AS
$BODY$

DECLARE
  old_column RECORD;  
    s text;
BEGIN

FOR old_column IN (
    SELECT
        isc.table_schema as table_schema,
        isc.table_name as table_name,
        isc.column_name as column_name
    FROM 
        information_schema.columns  isc
    INNER JOIN 
        pg_tables pt
            ON (isc.table_schema = pt.schemaname and isc.table_name = pt.tablename)
    WHERE
        isc.column_name like '%date%' and 
        isc.table_schema in ('public') and 
        isc.data_type = 'timestamp without time zone'
    ORDER BY
        isc.table_name ASC
    ) 
LOOP
    RAISE NOTICE 'Schema: %',old_column.table_schema;
    RAISE NOTICE 'Table: %',old_column.table_name;
    RAISE NOTICE 'Column %',old_column.column_name;


    EXECUTE 'ALTER TABLE '||old_column.table_schema||'.'||old_column.table_name||'
                ALTER COLUMN '||old_column.column_name||' TYPE timestamp(0) with time zone';

    RAISE NOTICE '-------------------------------------------------------------------------------';
    RAISE NOTICE '';
END LOOP;


RETURN 'S';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

But some views depend on affected columns and I got error:

ERROR:  cannot alter type of a column used by a view or rule

Also I got error about indexes.

How to change the data type for all timestamp without time zone columns?

Solution

  • To my knowledge, there is no built-in way to auto-change depending views when the datatype changes.
    I see two possibilities:

    1. DROP all depending views before the type change and recreate them afterwards.

    2. A completely different approach: Dump the database, change the table definition in the dump and restore it. As long as you do that in the same time zone, timestamp without time zone should be coerced to timestamptz(0) correctly. Note that the cast to timestamptz(0) rounds to full seconds.

    If you have more than a handful of objects, dump & restore is the way to go - if you can afford the downtime.

    Demo

    CREATE TEMP TABLE t  (id int, x timestamp);
CREATE TEMP TABLE t1 (id int, x timestamptz(0);

INSERT INTO t VALUES
 (1, '2019-05-25 13:23:03.123')  -- rounds down
 (1, '2019-05-25 13:23:03.987')  -- rounds up
,(2, '2019-05-25 23:23:03')
,(3, '2019-05-25 0:0:0');

COPY t  TO   '/var/lib/postgres/ts_test.sql';
COPY t1 FROM '/var/lib/postgres/ts_test.sql';

SELECT t.x, t.x::timestamptz(0), t1.x
FROM   t
JOIN   t1 USING (id);

    Coercion happens automatically and correctly.

    Related: