sql - postgres 批量数据类型更改

Question

我需要将整个数据库中的所有表列从数据类型 timestampwithouttime zone 更改为timestamp(0)withtime zone.
所以我创建了这个函数:

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;

但是一些 View 依赖于受影响的列，我得到了错误:

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

还有关于索引的错误。

如何更改所有 timestamp without time zone 列的数据类型？

Answer 1

据我所知，没有内置方法可以在数据类型更改时自动更改依赖 View 。
我看到两种可能性:

1. DROP 在类型更改之前所有依赖 View ，然后重新创建它们。

2. 一种完全不同的方法:转储数据库，更改转储中的表定义并恢复它。只要您在同一时区执行此操作，timestamp without time zone 就应该正确地强制转换为timestamptz(0)。请注意，转换为 timestamptz(0) rounds 为整秒。

如果您有多个对象，转储和恢复是可行的方法 - 如果您能承受停机时间。

演示

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);

强制转换会自动且正确地发生。

相关:

• Ignoring time zones altogether in Rails and PostgreSQL