PSQL common task

psql_task

-----------
-- Drop database connections
-----------

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'database_name'
  AND pid <> pg_backend_pid();
  
-----------
-- SEQUENCES
-----------
-- Get sequence name
SELECT pg_get_serial_sequence('table_name', 'field_name')

-- Create text sql, the result sql can show difference between actual sequence value and its max(id)
SELECT
  coalesce('select pg_catalog.setval(pg_get_serial_sequence('''||
  pg_class.relname || ''', ''' ||
  pg_attribute.attname || '''), (SELECT MAX(' || pg_attribute.attname || ') FROM ' ||
  pg_class.relname || ') +1 ); ', pg_class.relname)
FROM pg_index, pg_class, pg_attribute
WHERE
  pg_class.oid = pg_class.relname::regclass AND
  indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey) AND
      pg_class.relkind = 'r' and pg_class.relhaspkey
  AND indisprimary;