Resets all sequences in all schema in the current PostgreSQL database.

reset-all-sequences-in-db.sql

DO
$$
    DECLARE _sql                        text;
BEGIN
    WITH all_sequences
    AS
    (
        SELECT
            pg_namespace.nspname        AS schema_name,
            pg_class.relname            AS table_name,
            pg_class.oid                AS table_oid,
            pg_attribute.attname        AS column_name,
            substring(pg_attrdef.adsrc from E'^nextval\\(''([^'']*)''(?:::text|::regclass)?\\)') 
                                        AS seq_name 
        FROM pg_class
        INNER JOIN pg_attribute
        ON pg_class.oid = pg_attribute.attrelid
        INNER JOIN pg_attrdef
        ON 
        (
            pg_attribute.attrelid = pg_attrdef.adrelid
            AND pg_attribute.attnum = pg_attrdef.adnum
        ) 
        INNER JOIN pg_namespace
        ON pg_class.relnamespace = pg_namespace.oid
        WHERE has_schema_privilege(pg_namespace.oid,'USAGE')
        AND pg_namespace.nspname NOT LIKE 'pg!_%' ESCAPE '!'
        AND has_table_privilege(pg_class.oid,'SELECT')
        AND (NOT pg_attribute.attisdropped)
        AND pg_attrdef.adsrc ~ 'nextval'
    )
    SELECT 
        array_to_string
        (
            array_agg
            (
                'SELECT SETVAL(' ||
                quote_literal(seq_name) ||
                ', COALESCE(MAX(' ||quote_ident(column_name)|| '), 1) ) FROM ' ||
                quote_ident(schema_name)|| '.'||quote_ident(table_name)|| ';'
            ), E'\n'
        )
    INTO _sql
    FROM all_sequences;

    EXECUTE _sql;
END
$$
LANGUAGE plpgsql;