Clash Royale CLAN TAG#URR8PPP

Postgresql Sequence vs Serial

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

I never use serial before.

RETURNING

serial

lastval()

1 Answer
1

Check out a nice answer about 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:

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

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)

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

serial

OWNED

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.