Question

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;

但有些观点取决于受影响的栏目,

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

我对指数也有错误

如何更改所有 timestamp 的数据类型, 没有时区 列?

Answer 1

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

类型变更前所有依存的视图,然后“坚固”重新创建这些视图。
完全不同的方法 : < 坚固 > Dump < / 坚固 > 数据库, 更改垃圾堆中的表格定义, 并 < 坚固 > / 坚固 > 数据库。只要您在同一个时区 < / 坚固 > 进行此 " 坚固 ", 编码 > 时间标记没有时区 < / code > 应被强迫使用 < code> timestamptz(0) 正确。请注意, 投到 < code>timestamptz(0) < nuger > 回合 < / 坚固 > 的时间标记应被强制到全秒。

如果您有多个对象, 倾弃 & amp; 恢复是前进的道路 - 如果您能负担停机时间。

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

胁迫自动和正确地发生。

Demo

友情链接