pgsql 学习笔记（一）：批量修改数据表 id 的自增序列

前言：

我学习 pgsql 没有多久，对于数据库方面一知半解。开始写这个系列只是为了锻炼自己，有出错之处，敬请谅解，并请告知。

正文：

首先讲一下 pgsql 和 mysql 在 id 自增这一点上的不同：在 mysql 中，实现 id 自增的方式是依靠加一个 auto_increment 标志，而在 pgsql 中，id 自增是通过序列 SEQUENCE。

CREATE TABLE "oc_cms_staff" (
    "id" int4 NOT NULL DEFAULT nextval('oc_cms_staff_id_seq'::regclass),
    ...
};

其中的 oc_cms_staff_id_seq 即为序列。我之所以要去学习这篇文章的主题，也正是因为错误的数据库操作，导致了其中的数据表 id 依赖的序列出现混乱。
序列名通常为表名后加 “_id_seq”。为了达到本文的目的，我们先需要得到数据库中所有的表名，对应的查询语句如下：

SELECT tablename FROM pg_tables where tableowner='postgres' and SCHEMANAME='public'

得到了表名，我们就可以得到序列名，并针对序列进行修改。这包括了几步，第一步应该是删除之前该序列。但直接删除会报错，因为之前已经有数据表的 id 依赖于它，不能强行删除，删除前应该先将依赖解除。删除之后再重新创建，创建时应该设置它的起始值为 id 目前的最大值，否则会冲突。最后是重新给 id 绑定序列。

// 删除前先解除 id 对该序列的依赖
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT null;
DROP SEQUENCE IF EXISTS sequence_name;
// 这里的 id_max 即 id 目前的最大值，可通过 “SELECT MAX(id) FROM tablename” 得到
CREATE SEQUENCE sequence_name START WITH id_max;
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('sequence_name'::regclass);

这样就修改了数据表 id 的自增序列。知道了可以修改数据表 id 的自增序列的查询语句，接下来我们就可以写一个 PHP 脚本来批量修改了。

<?php

$host = 'your_host';
$port = 'your_port';
$dbname = 'your_dbname';
$user = 'your_user';
$password ='your_password';

// 连接数据库
$dbconn = pg_connect('host='.$host.' port='.$port.' dbname='.$dbname.' user='.$user.' password='.$password);

if ($dbconn === false) {
    exit('connect error!');
}

// 查询所有的数据表名
$result = pg_query($dbconn, "SELECT tablename FROM pg_tables where tableowner='postgres' and SCHEMANAME='public'");
$tablename_list = pg_fetch_all($result);

// 根据表名依次修改序列
foreach ($tablename_list as $item) {
    // 获得 id 目前的最大值
    $id_max_list = pg_fetch_all(pg_query($dbconn, "SELECT MAX(id) FROM ".$item['tablename']));
    $id_max = $id_max_list[0]['max'] + 1;

    // 依据表名拼接序列名
    $sequence = $item['tablename']."_id_seq";

    // 依次执行结束绑定、删除序列、重建序列、绑定序列
    $alter1_res = pg_query($dbconn, "ALTER TABLE ".$item['tablename']." ALTER COLUMN id SET DEFAULT null;");
    $alter2_res = pg_query($dbconn, "DROP SEQUENCE IF EXISTS ".$sequence.";");
    $alter3_res = pg_query($dbconn, "CREATE SEQUENCE ".$sequence." START WITH ".$id_max.";");
    $alter4_res = pg_query($dbconn, "ALTER TABLE ".$item['tablename']." ALTER COLUMN id SET DEFAULT nextval('".$sequence."'::regclass);");

    // 打印每句查询语句成功或失败
    echo $item['tablename'].' '.($alter1_res === false ? 'error:ALTER TABLE pg_cms_map ALTER COLUMN id SET DEFAULT null;' : 'success').'<br>';
    echo $item['tablename'].' '.($alter2_res === false ? 'error:DROP SEQUENCE IF EXISTS pg_cms_map_id_seq;' : 'success').'<br>';
    echo $item['tablename'].' '.($alter3_res === false ? 'error:CREATE SEQUENCE pg_cms_map_id_seq START WITH 23;' : 'success').'<br>';
    echo $item['tablename'].' '.($alter4_res === false ? 'error:ALTER TABLE pg_cms_map ALTER COLUMN id SET DEFAULT nextval(\'pg_cms_map_id_seq\'::regclass);' : 'success').'<br>';
}

好了，大功告成！