add postgres seq (sequence) to all table (automatic add sequence to all table in postgresql with php script[laravel])

README.md

automatic add sequence to all table in postgresql with php script [laravel]

run this code in laravel

change owner or schema or ...

code steps:

1. get all tables
2. get max id in each table
3. create seq
4. set seq current value
5. set owner for seq
6. add seq for id default value

postgres.php

<?php
use Illuminate\Support\Facades\DB;

    $schema = "public";
    $dbname = "admin_test_std";


//---------get all tables ----------------------------------------------------------------
        $sql = <<<SQL
SELECT * FROM information_schema.tables WHERE table_schema = '{$schema}' and table_catalog='{$dbname}' and table_type = 'BASE TABLE';
SQL;

        $tables = DB::select($sql);

        foreach ($tables as $table) {
            if ($table->table_name == "spatial_ref_sys" or $table->table_name == "test") {
                continue;
            }
            
//------------------get max id number ----------------------------------------------------------------
            $sql = <<<SQL
select max(id)+1 as max from {$schema}.{$table->table_name} ;
SQL;
            try {
                $max_id = DB::select($sql);
                $max_id = $max_id[0]->max;
            }catch ( QueryException $e){
                var_dump($e->getMessage());
                var_dump("-------------------------------------------------------------");
                var_dump("-------------------------------------------------------------");
                var_dump($table->table_name);
                var_dump("-------------------------------------------------------------");
                var_dump("-------------------------------------------------------------");
                continue;
            }
            $max_id = $max_id ? $max_id : 1;

            
//------------------create sequence ----------------------------------------------------------------
            $sql = <<<SQL
CREATE SEQUENCE "{$schema}"."{$table->table_name}_seq"
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
SQL;
            DB::select($sql);

            
//------------------get all tables ----------------------------------------------------------------
            $sql = <<<SQL
SELECT setval('"{$schema}"."{$table->table_name}_seq"', {$max_id}, false);
SQL;
            DB::select($sql);

            
//------------------ set column owner for seq ----------------------------------------------------------------
            $sql = <<<SQL
ALTER SEQUENCE "{$schema}"."{$table->table_name}_seq"
OWNED BY "{$schema}"."{$table->table_name}"."id";
SQL;
            DB::select($sql);

            
//------------------ set owner for seq  ----------------------------------------------------------------
            $sql = <<<SQL
ALTER SEQUENCE "{$schema}"."{$table->table_name}_seq" OWNER TO "postgres";
SQL;

            DB::select($sql);

            
//------------------ set id defualt value ----------------------------------------------------------------
            $sql = <<<SQL
ALTER TABLE "{$schema}"."{$table->table_name}"
  ALTER COLUMN "id" SET DEFAULT nextval('{$table->table_name}_seq'::regclass);
SQL;

            DB::select($sql);

        }

run this code in laravel