我从MySQL切换到PostgreSQL,我想知道我如何能有一个INT列与自动递增。我在PostgreSQL文档中看到了一个名为SERIAL的数据类型,但我在使用它时遇到了语法错误。
当前回答
虽然看起来序列与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. 手动设置Serial Column值会导致后续查询失败。
@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: Key (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上进行
其他回答
虽然看起来序列与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. 手动设置Serial Column值会导致后续查询失败。
@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: Key (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上进行
在所问问题的上下文中以及@sereja1c对注释的回复中,创建SERIAL隐式地创建序列,因此对于上面的示例-
CREATE TABLE foo (id SERIAL,bar varchar);
CREATE TABLE将隐式地为序列列foo.id创建序列foo_id_seq。因此,SERIAL[4字节]很容易使用,除非您的id需要特定的数据类型。
您可以使用任何其他整数数据类型,例如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;
最好使用您自己的数据类型,而不是用户串行数据类型。
这种方法肯定有效,我希望它能有所帮助:
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/
从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);
当使用默认生成的选项时,这本质上与现有的串行实现相同:
create table foo
(
id integer generated by default as identity
);
当手动提供值时,底层序列也需要手动调整—与串行列相同。
默认情况下,标识列不是主键(就像串行列一样)。如果应该是一个,则需要手动定义主键约束。
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何添加列,如果不存在PostgreSQL?
- 如何在Postgres中获得两个字段的MIN() ?
- 截断Postgres数据库中的所有表
- 如何连接列在Postgres选择?
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?
- PostgreSQL:角色不允许登录