PostgreSQL:琐碎的 INSERT 第一次失败，之后成功

Question

我对在下面显示的普通数据库中遇到的一个奇怪的 Postgres 问题感到困惑:如果我首先插入一个标签并明确指定其 ID，然后尝试插入另一个标签而不传递 ID，然后第二次插入失败。如果我第三次尝试(同样没有 ID)，插入成功。

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;

\c mydb

DROP SCHEMA public;
CREATE SCHEMA core;

CREATE TABLE core.tag
(
    id serial PRIMARY KEY,
    title text NOT NULL
);

-- this works: all columns specified explicitly
INSERT INTO core.tag(id, title) VALUES (1, 'known tag');

-- omitting the tag ID fails with
-- ERROR:  duplicate key value violates unique constraint "tag_pkey"
-- DETAIL:  Key (id)=(1) already exists.
INSERT INTO core.tag(title) VALUES ('unknown tag');

-- this works again ?!?
INSERT INTO core.tag(title) VALUES ('unknown tag');

该问题似乎只发生在新创建的数据库上，一旦发生，似乎不会再次发生。我从来没有遇到过这样的事情 - 到目前为止，我只是插入了带有或不带有显式 ID 和 AFAICS 的数据，从来没有像这样失败过...

有人知道这里发生了什么吗？!？

环境:Mac OSX 10.7.5 上的 PostgreSQL 9.1.3

Answer 1

这当然失败了。

会发生什么？

当您创建表时，还会创建一个序列来生成 ID 列的值。该序列以 1 开头，但仅当您未为 ID 列指定值时才使用它。

现在当你运行的时候

INSERT INTO core.tag(id, title) VALUES (1, 'known tag');

您绕过了 Postgres 自动分配 ID 值，序列“保持”为 1。

现在当你运行的时候

INSERT INTO core.tag(title) VALUES ('unknown tag');

Postgres 从序列中获取下一个值——1。但是它已经存在所以插入失败。从序列取值后，下一个值为2，所以后面不指定ID值的insert取2成功。

解决方案是永远不要在您的插入中包含 ID 列。或者 - 如果您这样做 - 从序列中请求 ID:

INSERT INTO core.tag(id, title) VALUES (nextval('tag_id_seq'), 'known tag');

创建序列列时，它会自动与名为 <table_name>_<column_name>_seq 的序列相关联.这就是我在上述声明中使用的名称。

有关串行“数据类型”如何工作的更多详细信息，请参见手册:http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL