35

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.

Michele Dorigatti
  • 736
  • 1
  • 6
  • 17
Se Song
  • 1,503
  • 2
  • 18
  • 31
  • If you want its serial or sequence assigned to what you just inserted, it's better to use the ```RETURNING``` statement. See http://stackoverflow.com/questions/19167349/postgresql-insert-from-select-returning-id – Alexandre TryHard Leblanc Dec 02 '15 at 04:04
  • 1
    A `serial` uses a sequence in the background. There is essentially no difference. Using `lastval()` right after your insert is just fine in both cases. – a_horse_with_no_name Dec 02 '15 at 22:01

2 Answers2

41

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 a 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 <tablename>_<fieldname>_seq. The above one is the 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

Community
  • 1
  • 1
zedfoxus
  • 32,227
  • 4
  • 59
  • 60
  • 8
    Yes, but `serial` is not an actual data type, it's a pseudo data type - which is an important distinction to make: http://stackoverflow.com/a/27309311/939860 or http://stackoverflow.com/a/14651788/939860 And it only makes sense to share a sequence in special situations, and you would use a free-standing sequence in such a case, not one that is `OWNED` by a serial column - thus avoiding the caveats you mention at the bottom of your answer. – Erwin Brandstetter Dec 02 '15 at 06:09
5

2021 answer using identity

I was wondering when it is better to choose sequence, and when it is better to use serial.

Not an answer to the whole question (only the part quoted above), still I guess it could help further readers. You should not use sequence nor serial, you should rather prefer identity columns:

create table apps (
    id integer primary key generated always as identity
);

See this detailed answer: https://stackoverflow.com/a/55300741/978690 (and also https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial)

rap-2-h
  • 26,857
  • 31
  • 150
  • 246