Finding Updated Records Across All PostgreSQL Tables

Finding Updated Records Across All PostgreSQL Tables

When troubleshooting data changes or auditing database activity, you often need to find recently updated records across your entire schema. This PostgreSQL stored procedure dynamically searches all tables containing an updated_at column.

DO $$ 
DECLARE 
    rec RECORD;
    query TEXT := '';
    search_date TEXT := '2025-02-11 15:3%';  -- Change this date as needed
BEGIN
    -- Loop through all tables containing the 'updated_at' column
    FOR rec IN 
        SELECT table_name 
        FROM information_schema.columns 
        WHERE column_name = 'updated_at' 
          AND table_schema = 'public'  -- Change schema if necessary
    LOOP
        -- Append each table query dynamically
        query := query || 
            FORMAT('SELECT ''%s'' AS table_name, id FROM %I WHERE updated_at::TEXT LIKE %L UNION ALL ', 
                    rec.table_name, rec.table_name, search_date);
    END LOOP;

    -- Remove last 'UNION ALL' to avoid syntax error
    IF query <> '' THEN
        query := LEFT(query, LENGTH(query) - 10);
        --EXECUTE query;
        RAISE NOTICE 'Generated Query: %', query;
    ELSE
        RAISE NOTICE 'No tables found with updated_at column';
    END IF;
END $$;

How It Works

1. Schema Discovery: Uses information_schema.columns to find all tables with an updated_at column
2. Dynamic Query Building: Constructs a UNION query across all matching tables
3. Safe SQL Generation: Uses FORMAT() with %I (identifier) and %L (literal) to prevent SQL injection
4. Flexible Date Matching: Converts timestamps to text for LIKE pattern matching

Usage Tips

• Modify search_date to match your timestamp format and search criteria
• Change table_schema if your tables are in a different schema
• Uncomment EXECUTE query; to run the generated query instead of just displaying it
• Consider adding additional columns to the SELECT if you need more context

This approach is particularly useful for debugging data synchronization issues or tracking down unexpected changes across a complex database schema.