Update sequence data

postgresq-update-sequence-data.sql

-- Updates sequence data with an offset of 5000
WITH sequences AS (
    SELECT *
    FROM (
        SELECT table_schema,
               table_name,
               column_name,
               pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) AS col_sequence
        FROM information_schema.columns
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    ) t
    WHERE col_sequence IS NOT NULL
), maxvals AS (
    SELECT table_schema,
           table_name,
           column_name,
           col_sequence,
           (xpath('/row/max/text()',
                  query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
           )[1]::text::bigint AS max_val
    FROM sequences
)
SELECT table_schema,
       table_name,
       column_name
       col_sequence,
       coalesce(max_val, 0) AS max_val,
       setval(col_sequence, coalesce(max_val + 5000, 1))
FROM maxvals;

-- Check if any sequence is missed out with the following query
-- All sequences must be non-NULL and > 1
SELECT schemaname as schema,
       sequencename as sequence,
       last_value
FROM pg_sequences
WHERE (last_value is NULL or last_value = 1)
ORDER BY schemaname, sequencename;