Last active
October 11, 2017 18:36
-
-
Save Nonymus/abbb4b29201e3ad72569efa223315776 to your computer and use it in GitHub Desktop.
Change all timestamp to timestamptz in schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION change_tz() RETURNS integer AS $$ | |
DECLARE | |
query varchar; | |
result integer := 0; | |
rel RECORD; | |
col RECORD; | |
BEGIN | |
FOR rel IN SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = 'public' AND data_type = 'timestamp without time zone' AND table_name not like 'databasechangelog%' LOOP | |
query := 'ALTER TABLE ' || quote_ident(rel.table_name) || ' SET WITHOUT CLUSTER'; -- NOOP, makes combining easier | |
FOR col IN SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND data_type = 'timestamp without time zone' AND table_name = rel.table_name LOOP | |
-- RAISE NOTICE 'Table: %, Column: %', quote_ident(entry.table_name), quote_ident(entry.column_name); | |
query := query || ', ALTER COLUMN ' || quote_ident(col.column_name) || ' TYPE timestamp with time zone USING ' || quote_ident(col.column_name) || ' AT TIME ZONE ''Europe/Berlin'''; | |
END LOOP; | |
RAISE NOTICE '%', query; | |
EXECUTE query; | |
END LOOP; | |
result := result + 1; | |
RETURN result; | |
END; | |
$$ LANGUAGE plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment