POST MIGRATION ajouter un booleen

# changer type en booleen
alter table "<your_table>"
alter column "<your_column>"
set data type boolean
using case
    when "<your_column>" = '1' then true
    when "<your_column>" = '0' then false
    else null
end;

 

alter database <dbname> set search_path = "$user", public, <schema> ;

# post migration pour un schema

for tablename in $(psql -U postgres -h <hostname> -d <dbname> -t -c "select table_name as _table from information_schema.tables t where t.table_schema='<schema>' order by _table asc"); 
do
 echo $tablename
 psql -U postgres -h <hostname> -d <dbname> -c "analyze ${tablename};"
 psql -U postgres -h <hostname> -d <dbname> -c "select count(*) ${tablename} from ${tablename};" >> rowcount.log
done

# post migration pour plusieurs schemas

#!/bin/sh
schema="('sche1','sche2','sche3','...')"

namespace="."
dbname="dbname"
host="hostname"

 for tablename in $(psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc");
        do
        echo "psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc""
        echo $tablename
          psql -U postgres -h ${host} -d ${dbname} -c "analyze ${tablename};"
          psql -U postgres -h ${host} -d ${dbname} -c "select count(*) as nr_rows, '${tablename}' as tablename from ${tablename};" >> logs/rowcount.log
 done





#update sequ

1-    Récupérer la séquence sur Oracle : 
SELECT sequence_name, last_number FROM user_sequences; 
2-    Mettre à jour la séquence sur Postgres : 
SELECT setval('<nom_sequence',<last_number> , true);