planzelle

Postgresql: Alter table SET WITHOUT OIDS for all databases/tables

Jun 30th, 2021 (edited)
274
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/bin/bash
  2. # remove OID from all tables in listed databases
  3. # before execute: sudo su postgres
  4.  
  5. echo "Start removing OIDs";
  6.  
  7. for db in `psql -qAt -c "SELECT datname FROM pg_database WHERE datname != 'postgres' AND datallowconn = true ORDER BY datname;" postgres` ; do
  8.  
  9.     DATABASE_NAME="$db";
  10.     echo "==============================";
  11.     echo $DATABASE_NAME;
  12.  
  13.     for tbl in `psql -qAt -c "SELECT schemaname || '.' || tablename FROM pg_tables WHERE schemaname <> 'pg_catalog' AND schemaname <> 'information_schema';" $DATABASE_NAME` ; do  
  14.         psql -c "ALTER TABLE $tbl SET WITHOUT OIDS" $DATABASE_NAME ;
  15.     done
  16.    
  17. done
  18.  
  19. echo "...done...";
  20.  
RAW Paste Data