Postgresql 序列与串行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34034702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql Sequence vs Serial
提问by Se Song
I was wondering when it is better to choose sequence, and when it is better to use serial.
我想知道什么时候选择序列更好，什么时候使用串行更好。
What I want is returning last value after insert using
我想要的是在插入后返回最后一个值使用
SELECT LASTVAL();
I read this question PostgreSQL Autoincrement
我读了这个问题 PostgreSQL Autoincrement
I never use serial before.
我以前从未使用过串行。
回答by zedfoxus
Check out a nice answer about Sequence vs. Serial
查看关于Sequence vs. Serial 的一个很好的答案
Sequence will just create sequence of unique numbers. It's not a datatype. It is a sequence. For example:
序列只会创建唯一数字的序列。它不是数据类型。它是一个序列。例如：
create sequence testing1;
select nextval('testing1'); -- 1
select nextval('testing1'); -- 2
You can use the same sequence in multiple places like this:
您可以在多个位置使用相同的序列，如下所示：
create sequence testing1;
create table table1(id int not null default nextval('testing1'), firstname varchar(20));
create table table2(id int not null default nextval('testing1'), firstname varchar(20));
insert into table1 (firstname) values ('tom'), ('henry');
insert into table2 (firstname) values ('tom'), ('henry');
select * from table1;
| id | firstname |
|----|-----------|
| 1 | tom |
| 2 | henry |
select * from table2;
| id | firstname |
|----|-----------|
| 3 | tom |
| 4 | henry |
Serial is a pseudo datatype. It will create sequence object. Let's take a look at a straight-forward table (similar to the one you will see in the link).
串行是一种伪数据类型。它将创建序列对象。让我们看一个简单的表格（类似于您将在链接中看到的表格）。
create table test(field1 serial);
This will cause a sequence to be created along with the table. The sequence name's nomenclature is __seq. The above one is equivalent of:
这将导致与表一起创建一个序列。序列名称的命名法是 __seq。上面的相当于：
create sequence test_field1_seq;
create table test(field1 int not null default nextval('test_field1_seq'));
Also see: http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
另见：http: //www.postgresql.org/docs/9.3/static/datatype-numeric.html
You can reuse the sequence that is auto-created by serial datatype, or you may choose to just use one serial/sequence per table.
您可以重用由串行数据类型自动创建的序列，或者您可以选择只使用每个表一个串行/序列。
create table table3(id serial, firstname varchar(20));
create table table4(id int not null default nextval('table3_id_seq'), firstname varchar(20));
(The risk here is that if table3 is dropped and we continue using table3's sequence, we will get an error)
（这里的风险是如果 table3 被删除，我们继续使用 table3 的序列，我们会得到一个错误）
create table table5(id serial, firstname varchar(20));
insert into table3 (firstname) values ('tom'), ('henry');
insert into table4 (firstname) values ('tom'), ('henry');
insert into table5 (firstname) values ('tom'), ('henry');
select * from table3;
| id | firstname |
|----|-----------|
| 1 | tom |
| 2 | henry |
select * from table4; -- this uses sequence created in table3
| id | firstname |
|----|-----------|
| 3 | tom |
| 4 | henry |
select * from table5;
| id | firstname |
|----|-----------|
| 1 | tom |
| 2 | henry |
Feel free to try out an example: http://sqlfiddle.com/#!15/074ac/1
随意尝试一个例子：http: //sqlfiddle.com/#!15/074ac/1
