run this code in laravel
change owner or schema or ...
code steps:
- get all tables
- get max id in each table
- create seq
- set seq current value
- set owner for seq
- add seq for id default value
<?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