PostgreSQL自动增量

9 回答

• 14

  是的，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更改为现有列 .

  回复于 2024-05-11T00:35:08+08:00
• 13

  您可以使用任何其他integer data type，例如 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;
  

  最好使用您自己的数据类型，而不是用户serial data type .

  回复于 2024-05-11T00:35:08+08:00
• 34

  如果要在已存在的表中向id添加序列，可以使用：

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

  回复于 2024-05-11T00:35:08+08:00
• 16

  虽然看起来序列等同于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);
  

  您应该使用 no fragementation 获得以下内容：

  +-----+-------+
  | 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”DETAIL：键（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

  回复于 2024-05-11T00:35:08+08:00
• 210

  从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 列一样） . 如果它应该是1，则需要手动定义主键约束 .

  回复于 2024-05-11T00:35:08+08:00
• 24

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

  回复于 2024-05-11T00:35:08+08:00
• 635

  在问题的背景下，@ sereja1c回复评论，创建 SERIAL 隐式创建序列，所以对于上面的例子 -

  CREATE TABLE foo (id SERIAL,bar varchar);
  

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

  回复于 2024-05-11T00:35:08+08:00
• 87

  很抱歉，重新提出一个旧问题，但这是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,
  );
  

  希望有所帮助:)

  回复于 2024-05-11T00:35:08+08:00
• 2

  这种方式肯定会起作用，我希望它有所帮助：

  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/

  回复于 2024-05-11T00:35:08+08:00