postgres 用 INSERT INTO 复制数据后，如何让新数据的 id 从最大开始？

Question

django 项目，新建了一个表 new_project，然后用下面的语句从 old_project 复制了一堆数据到新表 new_project 里：

INSERT INTO new_project (id, name, description, enable)
SELECT id, name, description, enable
FROM old_project;

但是，如果再插入新数据时，该表的 id 不会从最大的 id 开始递增，而是从 1 开始导致报错 duplicate key value violates unique constraint。

应该如何设置呢？

Answer 1

由于使用的是 django 框架，所以 django 官方有现成的命令可直接使用：

python manage.py sqlsequencereset new_project | python manage.py dbshell

直接执行上面的命令即可重建 id 起始值

Answer 2

id 是否使用了序列？ 如果使用了序列，修改序列的 start 值即可，参考

Command:     ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE [ IF EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART [ [ WITH ] restart ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

Answer 3

postgres=# select * from tab;
 a | b | c | d
---+---+---+---
 1 |   |   |
 2 |   |   |
 3 |   |   |
 4 |   |   |
 5 |   |   |
 6 |   |   |
(6 rows)


postgres=# with max_id as (select max(a) as id from tab) insert into tab select b.id+(row_number() over() ),a,b.id from tab a, max_id b;
INSERT 0 6

postgres=# select * from tab;
 a  | b | c | d
----+---+---+---
  1 |   |   |
  2 |   |   |
  3 |   |   |
  4 |   |   |
  5 |   |   |
  6 |   |   |
  7 | 1 | 6 |
  8 | 2 | 6 |
  9 | 3 | 6 |
 10 | 4 | 6 |
 11 | 5 | 6 |
 12 | 6 | 6 |
(12 rows)


postgres=#