postgres bulk data type change

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;

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?

Erwin Brandstetter · Accepted Answer · 2019-05-03T21:27:58.387

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

DROP all depending views before the type change and recreate them afterwards.
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.

Ignoring time zones altogether in Rails and PostgreSQL

IMHO dump+edit+restore is the only sane thing to do. Besides views/rules, there are also references that are "invisible" to the schema, such as functions and triggers. And: having separate --schema-only and --data-only dumps, editing is no more than a sed-job. — wildplasser, May 25 '12 at 11:28
@wildplasser: I agree, for more than a hand full of objects, this is the way to go. — Erwin Brandstetter, May 25 '12 at 11:35

score 1 · Answer 2 · answered May 25 '12 at 11:23

1

You could try it with pg_dump

first make a dump with --format=plain --schema-only

Change the data-type in the dump file and restore it in a new db.

Than you make a dump with --data-only and restore them in the new db.

answered May 25 '12 at 11:23

Thomas

1,026
7
18

postgres bulk data type change

2 Answers2

Demo