与 MySQL AUTO INCREMENT 等效的 PostgreSQL 数据类型是什么？

Question

我正在从 MySQL 切换到 PostgreSQL，我想知道如何才能拥有带有 AUTO INCRMENT 的 INT 列。 我在 PostgreSQL 文档中看到一种名为 SERIAL 的数据类型，但在使用它时出现语法错误。

Answer 1

是的，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。

Answer 2

您可以使用任何其他整数数据类型，例如小整数。

示例：

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;

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

Answer 3

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

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

Answer 4

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

当使用默认生成选项时，这与现有生成的行为本质上相同code>serial 实现：

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

当手动提供值时，也需要手动调整底层序列 - 与 serial 列相同。

---

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

Answer 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，稍后会发生“冲突”。

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

Answer 6

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

希望有帮助:)

Answer 7

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

Answer 8

从 PostgreSQL 10 开始

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

Answer 9

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

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE将隐式创建序列< code>foo_id_seq 用于串行列 foo.id。 因此，SERIAL [4 Bytes] 易于使用，除非您的 id 需要特定的数据类型。

Answer 10

这种方法肯定有效，我希望它有所帮助：

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/

Answer 11

创建序列。

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