PostgreSQL自动增量

是的,串行是等效函数。

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 |
+----------+

串行只是一个围绕序列的创建表时间宏。不能将序列号更改为现有列。

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 .

如果要将序列添加到已存在的表中的id,可以使用:

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

相等的 对于MySQL自动增量,有一些细微但重要的区别:

1.查询序列/序列增量失败

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;

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)

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

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

详细信息:键(uid)=(5)已存在。

相比之下,MySQL将优雅地处理此问题,如下所示:

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

现在插入另一行而不设置uid

INSERT INTO table1 (col_b) VALUES(3);

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

当手动提供值时,底层序列也需要手动调整-与使用 柱

默认情况下,标识列不是主键(就像 电视连续剧

https://blog.2ndquadrant.com/postgresql-10-identity-columns/

这恰好是:

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

希望有帮助:)

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

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

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE 将隐式创建序列 foo_id_seq 对于串行列 foo.id 电视连续剧

自PostgreSQL 10以来

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

这种方法肯定会奏效,我希望它有助于:

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/

创建序列。

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