postgresql - 主键序列不存在

Question

我有一个 postgresql 数据库，在迁移到新版本并导入旧数据后，我的主键出现问题:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint   
"browser_link_pkey"  Detail: Key (id)=(173) already exists.

所以我想重置我的序列，但正在运行:

select nextval('browser_link_id_seq')

也失败:

column „browser_link_id_seq“ does not exist
SQL Status:42703

这是创建表的SQL

CREATE TABLE browser_link (
    id bigint NOT NULL,
....
);
ALTER TABLE ONLY browser_link
ADD CONSTRAINT browser_link_pkey PRIMARY KEY (id);

我尝试选择串行序列，但似乎不存在:

postgres=# \connect historify
You are now connected to database "historify" as user "postgres".
historify=# select pg_get_serial_sequence('browser_link', 'id');
 pg_get_serial_sequence
------------------------

(1 row)

我正在使用 postgresql 9.5.3。另外，在发生错误之前，id 列确实按预期增加，因此它确实可以工作。

现在我的两个问题:

1. 为什么自动增量会起作用？
2. 如何重置自动增量计数器？

Answer 1

Postgres 中的序列是具有默认由序列提供的整数。在以下片段中，表“一”和表“二”完全相同:

CREATE TABLE one
( id bigserial NOT NULL PRIMARY KEY
  , name varchar
);

CREATE TABLE two
( id bigint NOT NULL PRIMARY KEY
  , name varchar
);
CREATE SEQUENCE "two_id_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER SEQUENCE "two_id_seq"
        OWNED BY two.id
        ;
ALTER TABLE two
        ALTER COLUMN id SET default nextval('two_id_seq')
        ;

\d one
\d two

---

如您所见，serial one 的语法表更加紧凑(它实际上是 two 表所需的 4 个语句的简写符号)

因此，在问题表中，您可以使用 bigserial 重建表而不是bigint作为数据类型，或将现有序列“附加”到现有 id通过ALTER table ... ALTER COLUMN ...专栏语法。

---

您可以通过 'setval('name', val);` 将序列设置为新值:

INSERT INTO two(id, name) VALUES ( 13, 'thirteen' );
select nextval('two_id_seq');
SELECT setval ('two_id_seq', (select max(id) FROM two));
select * from two;
select nextval('two_id_seq');

---

结果:

INSERT 0 1
 nextval 
---------
       1
(1 row)

 setval 
--------
     13
(1 row)

 id |   name   
----+----------
 13 | thirteen
(1 row)

 nextval 
---------
      14
(1 row)