什么是PostgreSQL数据类型相当于MySQL自动增长？

我正在从MySQL切换到PostgreSQL，我想知道如何使用AUTO INCREMENT来创建INT列。我在PostgreSQL文档中看到了一个名为SERIAL的数据类型，但我在使用它时会出现语法错误。

747780 次浏览
小开
最佳答案

是的，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更改到现有列上。

小开

您可以使用任何其他整数数据类型，例如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;

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

小开

在所问问题的上下文中并回复@sereja1c的评论，创建SERIAL隐式创建序列，因此对于上述示例-

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE将隐式地为序列列foo.id创建序列foo_id_seq。因此，除非您需要ID的特定数据类型，否则SERIAL[4字节]对其易用性有好处。

小开

如果您想将序列添加到已经存在的表中的id，您可以使用： 

CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');
小开

您必须小心不要直接插入到您的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;
小开

虽然看起来序列是相当到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，稍后将“冲突”。

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);

它将失败并显示以下错误消息：

错误：重复键值违反唯一约束"table1_pkey" 详细信息：键（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

小开

从Postgres 10开始，还支持SQL标准定义的标识列：

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

创建一个除非明确要求否则无法覆盖的标识列。以下插入将失败，并定义为generated always的列： 

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
);

当手动提供值时，也需要手动调整基础序列——与serial列相同。

默认情况下，标识列不是主键（就像serial列一样）。如果它应该是一个，则需要手动定义主键约束。

小开

抱歉，重复一个老问题，但这是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,
);

希望有帮助：）

小开

这种方式肯定会奏效，我希望它能帮助：

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/

小开

自PostgreSQL 10

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

创建序列。

CREATE SEQUENCE user_role_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 3
CACHE 1;
ALTER TABLE user_role_id_seq
OWNER TO postgres;

和修改表

ALTER TABLE user_roles ALTER COLUMN user_role_id SET DEFAULT nextval('user_role_id_seq'::regclass);

提交答案