How to move all timestamptz dates on the postgresql database?-CodePudding

I have a postgresql dump of some seed database. This dump was created few months ago so all data is there are about the past. It is not very convenient to develop on the past data because I have to always scroll in UI to that past date.

I was thinking to automatically shift every timestamptz field in the database by specific offset. It sounds doable via some script which will go throw database schema, find every timestamptz field, and then build a SQL update for every field.

So, are there any ready-made solutions for this?

CodePudding user response：

just add things to the database and then update it with this query, change the column name, table name and the amount of days you want it increamented by

UPDATE table_name
SET timestamptz = timestamptz   interval '1' day
WHERE 1 = 1;

CodePudding user response：

I solved it using this SQL query:

--
-- This SQL query shift all timestamptz fields in the database
--
--

BEGIN;

DO $$
declare
    sql_query text;    
    table_row record;
    column_row record;
    trigger_row record;
BEGIN
  FOR table_row IN (
    SELECT table_schema, table_name 
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE' AND table_schema = 'public'
  ) LOOP
    sql_query := '';

    RAISE NOTICE 'Checking %', table_row.table_name;
    
    FOR column_row IN (
        SELECT column_name
        FROM information_schema.columns 
        WHERE           
          table_schema = table_row.table_schema
          AND table_name = table_row.table_name
          AND udt_name = 'timestamptz'
          AND is_updatable = 'YES'
    ) LOOP
      sql_query := sql_query || 
         '"' || column_row.column_name || '" = "' || column_row.column_name || '"   interval ''100'' day,';      
    END LOOP;

    IF sql_query != '' THEN
        sql_query := substr(sql_query,1, length(sql_query)-1); -- Remove last ","

        sql_query := 'UPDATE ' || table_row.table_schema || '.' || table_row.table_name || ' SET ' || sql_query || ';';

        -- There might be some triggers which so let's disable them before update
        FOR trigger_row IN (
          SELECT trigger_name FROM information_schema.triggers WHERE
            trigger_schema = table_row.table_schema
            AND event_object_table = table_row.table_name
            AND event_manipulation = 'UPDATE' and 
             (action_timing = 'BEFORE' or action_timing = 'AFTER')
        ) LOOP
            sql_query := 'alter table ' || table_row.table_schema || '.' || table_row.table_name || 
             ' disable trigger ' || trigger_row.trigger_name || ';' || 
             sql_query ||
            'alter table ' || table_row.table_schema || '.' || table_row.table_name || 
             ' enable trigger ' || trigger_row.trigger_name || ';';            
        END LOOP;

        -- Same for the row level security, disable it if it was enabled
        IF (SELECT pg_class.oid FROM pg_class 
        LEFT JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_class.relnamespace
        WHERE relname = table_row.table_name AND
        pg_catalog.pg_namespace.nspname = table_row.table_schema AND relrowsecurity) IS NOT NULL THEN
            sql_query := 'alter table ' || table_row.table_schema || '.' || table_row.table_name || 
             ' disable row level security;' || 
             sql_query ||
            'alter table ' || table_row.table_schema || '.' || table_row.table_name || 
             ' enable row level security;';
        END IF;
        
        RAISE NOTICE '   %', sql_query;
        EXECUTE sql_query;
        RAISE NOTICE '---------------------------';
    END IF;   
  END LOOP;
END$$;


COMMIT;