PostgreSQL 自增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/787722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Autoincrement
提问by Ian
I'm switching from MySQL to PostgreSQL and was wondering how I can do autoincrement values. I saw in the PostgreSQL docs a datatype "serial", but I get syntax errors when using it (in v8.0).
我正在从 MySQL 切换到 PostgreSQL，并且想知道如何进行自动增量值。我在 PostgreSQL 文档中看到一个数据类型“串行”，但在使用它时出现语法错误（在 v8.0 中）。
回答by Trey
Yes, SERIAL is the equivalent function.
是的，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 is just a create table time macro around sequences. You can not alter SERIAL onto an existing column.
SERIAL 只是一个围绕序列的创建表时间宏。您不能将 SERIAL 更改为现有列。
回答by Ahmad
You can use any other integer data type, such as smallint.
您可以使用任何其他整数数据类型，例如smallint.
Example :
例子 ：
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;
Better to use your own data type, rather than user serial data type.
最好使用您自己的数据类型，而不是用户串行数据类型。
回答by sereja
If you want to add sequence to id in the table which already exist you can use:
如果要将序列添加到已存在的表中的 id ，您可以使用：
CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');
回答by Programster
Whilst it looks like sequences are the equivalentto MySQL auto_increment, there are some subtle but important differences:
虽然看起来序列等同于 MySQL auto_increment，但有一些微妙但重要的区别：
1. Failed Queries Increment The Sequence/Serial
1. 失败查询增加序列/序列
The serial column gets incremented on failed queries. This leads to fragmentation from failed queries, not just row deletions. For example, run the following queries on your PostgreSQL database:
串行列在查询失败时增加。这会导致失败查询的碎片化，而不仅仅是行删除。例如，在 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;
You should get the following output:
你应该得到以下输出：
uid | col_b
-----+-------
1 | 1
3 | 2
(2 rows)
Notice how uid goes from 1 to 3 instead of 1 to 2.
注意 uid 如何从 1 到 3 而不是 1 到 2。
This still occurs if you were to manually create your own sequence with:
如果您要手动创建自己的序列，这种情况仍然会发生：
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;
If you wish to test how MySQL is different, run the following on a MySQL database:
如果您想测试 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);
You should get the following with no fragementation:
您应该在没有碎片的情况下获得以下内容：
+-----+-------+
| uid | col_b |
+-----+-------+
| 1 | 1 |
| 2 | 2 |
+-----+-------+
2 rows in set (0.00 sec)
2. Manually Setting the Serial Column Value Can Cause Future Queries to Fail.
2. 手动设置串行列值可能会导致以后的查询失败。
This was pointed out by @trev in a previous answer.
@trev 在之前的回答中指出了这一点。
To simulate this manually set the uid to 4 which will "clash" later.
为了模拟这一点，手动将 uid 设置为 4，这将在稍后“冲突”。
INSERT INTO table1 (uid, col_b) VALUES(5, 5);
Table data:
表数据：
uid | col_b
-----+-------
1 | 1
3 | 2
5 | 5
(3 rows)
Run another insert:
运行另一个插入：
INSERT INTO table1 (col_b) VALUES(6);
Table data:
表数据：
uid | col_b
-----+-------
1 | 1
3 | 2
5 | 5
4 | 6
Now if you run another insert:
现在，如果您运行另一个插入：
INSERT INTO table1 (col_b) VALUES(7);
It will fail with the following error message:
它将失败并显示以下错误消息：
ERROR: duplicate key value violates unique constraint "table1_pkey" DETAIL: Key (uid)=(5) already exists.
错误：重复键值违反唯一约束“table1_pkey”详细信息：键 (uid)=(5) 已经存在。
In contrast, MySQL will handle this gracefully as shown below:
相比之下，MySQL 会优雅地处理这个问题，如下所示：
INSERT INTO table1 (uid, col_b) VALUES(4, 4);
Now insert another row without setting uid
现在插入另一行而不设置 uid
INSERT INTO table1 (col_b) VALUES(3);
The query doesn't fail, uid just jumps to 5:
查询不会失败，uid 只是跳转到 5：
+-----+-------+
| uid | col_b |
+-----+-------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
| 5 | 3 |
+-----+-------+
Testing was performed on MySQL 5.6.33, for Linux (x86_64) and PostgreSQL 9.4.9
测试在 MySQL 5.6.33 上执行，适用于 Linux (x86_64) 和 PostgreSQL 9.4.9
回答by a_horse_with_no_name
Starting with Postgres 10, identity columns as defined by the SQL standard are also supported:
从 Postgres 10 开始，还支持 SQL 标准定义的标识列：
create table foo
(
id integer generated always as identity
);
creates an identity column that can't be overridden unless explicitly asked for. The following insert will fail with a column defined as generated always:
创建一个标识列，除非明确要求，否则该列不能被覆盖。以下插入将失败，列定义为generated always：
insert into foo (id)
values (1);
This can however be overruled:
然而，这可以被否决：
insert into foo (id) overriding system value
values (1);
When using the option generated by defaultthis is essentially the same behaviour as the existing serialimplementation:
使用该选项时，generated by default这与现有serial实现的行为基本相同：
create table foo
(
id integer generated by default as identity
);
When a value is supplied manually, the underlying sequence needs to be adjusted manually as well - the same as with a serialcolumn.
当手动提供值时，底层序列也需要手动调整 - 与serial列相同。
An identity column is not a primary key by default (just like a serialcolumn). If it should be one, a primary key constraint needs to be defined manually.
默认情况下，标识列不是主键（就像serial列一样）。如果应该是 1，则需要手动定义主键约束。
回答by Zhao Li
Sorry, to rehash an old question, but this was the first Stack Overflow question/answer that popped up on Google.
抱歉，重提一个老问题，但这是 Google 上出现的第一个 Stack Overflow 问题/答案。
This post (which came up first on Google) talks about using the more updated syntax for PostgreSQL 10: https://blog.2ndquadrant.com/postgresql-10-identity-columns/
这篇文章（首先出现在 Google 上）讨论了为 PostgreSQL 10 使用更新的语法：https: //blog.2ndquadrant.com/postgresql-10-identity-columns/
which happens to be:
这恰好是：
CREATE TABLE test_new (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
);
Hope that helps :)
希望有帮助:)
回答by trev
You have to be careful not to insert directly into your SERIAL or sequence field, otherwise your write will fail when the sequence reaches the inserted value:
您必须注意不要直接插入到您的 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;
回答by Prince
In the context of the asked question and in reply to the comment by @sereja1c, creating SERIALimplicitly creates sequences, so for the above example-
在提出的问题的上下文中并回复@sereja1c 的评论，创建SERIAL隐式创建序列，因此对于上面的示例-
CREATE TABLE foo (id SERIAL,bar varchar);
CREATE TABLEwould implicitly create sequence foo_id_seqfor serial column foo.id. Hence, SERIAL[4 Bytes] is good for its ease of use unless you need a specific datatype for your id.
CREATE TABLE将为foo_id_seqserial column隐式创建序列foo.id。因此，SERIAL除非您的 ID 需要特定的数据类型，否则 [4 Bytes] 的易用性很好。
回答by webtechnelson
This way will work for sure, I hope it helps:
这种方式肯定会起作用，我希望它有所帮助：
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');
You can check this the details in the next link: http://www.postgresqltutorial.com/postgresql-serial/
您可以在下一个链接中查看详细信息：http: //www.postgresqltutorial.com/postgresql-serial/
回答by Sergey Vishnevetskiy
Since PostgreSQL 10
从 PostgreSQL 10 开始
CREATE TABLE test_new (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
payload text
);
