identiception: reset all IDENTITY columns in postgres

identiception.sh

# a query that finds all IDENTITY columns and then outputs
#   queries that find the max value for each table and then output
#     an appropriate alter table call for each one
# which we then run 3x through psql 😅🤯
#
# this assumes you have appropriate PG* variables set up, e.g.
# PGDATABASE=mydb
# PGHOST=localhost
# ...
cat<<EOF|psql -t -A|psql -t -A|psql -q
  SELECT
    'SELECT
      ''ALTER TABLE "' || table_name || '"
        ALTER COLUMN "' || column_name || '"
        RESTART '' || "' || column_name || '" + 1 || '';''
     FROM "' || table_name || '"
     ORDER BY "' || column_name || '" DESC LIMIT 1;'
  FROM information_schema.columns
  WHERE is_identity = 'YES' AND table_schema = 'public';
EOF