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.
There is essentially no difference.
A sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification. To create a sequence in PostgreSQL, you use the CREATE SEQUENCE statement.
The PostgreSQL Sequence is used as a Sequence object for creating the list of sequences. We have used the CREATE SEQUENCE command to create a new sequence number. In this section, we also understand how to create an ascending and descending sequence with the CREATE SEQUENCE.
A sequence in PostgreSQL is a user-defined schema-bound object that yields a sequence of integers based on a specified specification. The CREATE SEQUENCE statement is used to create sequences in PostgreSQL. Now let's analyze the above syntax: First, set the name of the sequence after the CREATE SEQUENCE clause.
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With