PostgreSQL的自动递增

我正在从MySQL切换到PostgreSQL，我想知道如何才能做到自动递增值。我在PostgreSQL的文档中看到了一个数据类型"serial"，但我在使用它时遇到了语法错误（在V8.0中）。

JasonMArcher
已编辑的问题 5日 一月 2015 в 7:47
编程
postgresql auto-increment
24日 四月 2009 в 10:00
9 种观点
对该问题的评论 (5)
解决办法

是的，SERIAL是等同的函数。

CREATE TABLE foo (
id SERIAL,
bar varchar);

INSERT INTO foo (bar) values ('blah');
INSERT INTO foo (bar) values ('blah');

SELECT * FROM foo;

1,blah
2,blah

SERIAL只是一个围绕序列的创建表时间宏。 你不能在现有列上改变SERIAL。

Nux
编辑本段答案10日 十一月 2016 в 1:09
评论(15)

你可以使用任何其他的整数数据类型，例如smallint。

例子:

CREATE SEQUENCE user_id_seq;
CREATE TABLE user (
    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
);
ALTER SEQUENCE user_id_seq OWNED BY user.user_id;

最好使用自己的数据类型，而不是用户串行数据类型。

user272735
编辑本段答案18日 五月 2012 в 10:43
评论(9)

如果你想在表中的id中添加序列，你可以使用。

CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');
Felipe
编辑本段答案4日 六月 2013 в 1:24
评论(5)

虽然看起来序列与MySQL的auto_increment是*等价的，但也有一些微妙但重要的区别。

1.

失败的查询会增加序列/序列。 序列在查询失败时被递增。 这将导致失败的查询导致碎片化，而不仅仅是删除行。 例如，在PostgreSQL数据库中运行以下查询。

CREATE TABLE table1 (
  uid serial NOT NULL PRIMARY KEY,
  col_b integer NOT NULL,
  CHECK (col_b>=0)
);

INSERT INTO table1 (col_b) VALUES(1);
INSERT INTO table1 (col_b) VALUES(-1);
INSERT INTO table1 (col_b) VALUES(2);

SELECT * FROM table1;

您应该得到以下输出。

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
(2 rows)

请注意 uid 如何从 1 到 3 而不是 1 到 2。

如果您要手动创建自己的序列，这种情况仍然会发生。

CREATE SEQUENCE table1_seq;
CREATE TABLE table1 (
    col_a smallint NOT NULL DEFAULT nextval('table1_seq'),
    col_b integer NOT NULL,
    CHECK (col_b>=0)
);
ALTER SEQUENCE table1_seq OWNED BY table1.col_a;

如果你想测试MySQL有什么不同，请在MySQL数据库上运行以下内容。

CREATE TABLE table1 (
  uid int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  col_b int unsigned NOT NULL
);

INSERT INTO table1 (col_b) VALUES(1);
INSERT INTO table1 (col_b) VALUES(-1);
INSERT INTO table1 (col_b) VALUES(2);

您应该得到以下内容，并且没有出现碎裂。

+-----+-------+
| uid | col_b |
+-----+-------+
|   1 |     1 |
|   2 |     2 |
+-----+-------+
2 rows in set (0.00 sec)

2.

手动设置序列列的值会导致未来的查询失败。

@trev在之前的回答中指出了这个问题。

为了模拟这种情况，手动将uid设置为4，这样会"冲突&quot。 以后会发生冲突。

INSERT INTO table1 (uid, col_b) VALUES(5, 5);

表数据。

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
   5 |     5
(3 rows)

运行另一个插件。

INSERT INTO table1 (col_b) VALUES(6);

表数据。

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
   5 |     5
   4 |     6

现在如果你运行另一个插入。

INSERT INTO table1 (col_b) VALUES(7);

它将失败，并出现以下错误信息。

ERROR.重复键值违反唯一约束"table1_pkey"。 重复的键值违反了唯一约束"table1_pkey&quot。 错误：重复键值违反唯一约束"table1_pkey"。 详情。 Key (uid)=(5)已经存在。

相比之下，MySQL会优雅地处理，如下图所示。

INSERT INTO table1 (uid, col_b) VALUES(4, 4);

现在插入另一行，不需要设置uid

INSERT INTO table1 (col_b) VALUES(3);

查询不会失败，uid只是跳到5。

+-----+-------+
| uid | col_b |
+-----+-------+
|   1 |     1 |
|   2 |     2 |
|   4 |     4 |
|   5 |     3 |
+-----+-------+

测试在MySQL 5.6.33、Linux（x86_64）和PostgreSQL 9.4.9上进行。

Programster
编辑本段答案8日 十月 2016 в 9:19
评论(2)

从Postgres 10开始，也支持SQL标准定义的身份列。

create table foo 
(
  id integer generated always as identity
);

创建一个身份列，除非明确要求，否则不能被覆盖。 下面的插入将失败，因为列定义为 "总是生成"。

insert into foo (id) 
values (1);

然而，这一点可以被否决。

insert into foo (id) overriding system value 
values (1);

当使用 "generated by default "选项时，这与现有的 "serial "实现的行为基本相同。

create table foo 
(
  id integer generated by default as identity
);

当手动提供一个值时，基本序列也需要手动调整--与 "序列 "列一样。

身份列默认不是主键（就像 "序列 "列一样）。 如果它应该是主键，则需要手动定义一个主键约束。

评论(0)

抱歉，重提一个老问题，但这是Google上出现的第一个Stack Overflow问题/答案。

这篇文章（在Google上最先出现的）谈到了如何使用PostgreSQL 10的最新语法。 https://blog.2ndquadrant.com/postgresql-10-identity-columns/

这恰好是。

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
);

希望能帮到你:)

评论(3)

你必须注意不要直接插入SERIAL或序列字段，否则当序列达到插入的值时，你的写入将失败。

-- Table: "test"

-- DROP TABLE test;

CREATE TABLE test
(
  "ID" SERIAL,
  "Rank" integer NOT NULL,
  "GermanHeadword" "text" [] NOT NULL,
  "PartOfSpeech" "text" NOT NULL,
  "ExampleSentence" "text" NOT NULL,
  "EnglishGloss" "text"[] NOT NULL,
  CONSTRAINT "PKey" PRIMARY KEY ("ID", "Rank")
)
WITH (
  OIDS=FALSE
);
-- ALTER TABLE test OWNER TO postgres;
 INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (1, '{"der", "die", "das", "den", "dem", "des"}', 'art', 'Der Mann küsst die Frau und das Kind schaut zu', '{"the", "of the" }');

 INSERT INTO test("ID", "Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (2, 1, '{"der", "die", "das"}', 'pron', 'Das ist mein Fahrrad', '{"that", "those"}');

 INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (1, '{"der", "die", "das"}', 'pron', 'Die Frau, die nebenen wohnt, heißt Renate', '{"that", "who"}');

SELECT * from test;
评论(0)

在所问问题的背景下，并在回复@sereja1c的评论时，创建`SERIAL'隐含地创建了序列，所以对于上述例子--

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE将隐含地创建序列foo_id_seq，用于序列列foo.id。因此，`SERIAL'[4字节]因其易于使用而很好，除非你需要一个特定的数据类型作为你的id。

Prince
编辑本段答案24日 二月 2015 в 7:38
评论(0)

这种方式肯定能行，希望能帮到你。

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');

or

INSERT INTO fruits VALUES(DEFAULT,'apple');

你可以在接下来的链接中查看这个细节。 http://www.postgresqltutorial.com/postgresql-serial/

评论(0)

从PostgreSQL 10开始

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);
评论(0)