This is a very quick tutorial on how autoincrementing fields in postgresql work.
In Postgresql, an autoincrementing column is implemented using a sequence generator and a default clause (or trigger if you want to get fancy). A sequence generator is actually just a special type of one row table that only holds one value. Since it only has one row with one value, it can be locked and unlocked very quickly during transactions.
Autoincrements can be done by hand, or by use of the macro SERIAL. Since postgresql version 7.3 the use of the SERIAL macro has been tracked in the postgresql dependency tracking system, and the sequences generated to support the autoincrementing column are automatically dropped when the column or its parent table are dropped. In 7.2 and before, it was the responsibility of the user to drop the sequences thus created.
Here is an example of an autoincrementing column being created with the SERIAL macro:
=> create table test (id serial, info text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id"
CREATE TABLE
=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('public.test_id_seq'::text)
info | text |
Notice how the column is automagically created as an int type, and a default is set to insert a number in it for us should we not supply one. Let's insert some data:
=> insert into test (id,info) values (DEFAULT,'fred');
INSERT 7322549 1
marl8412=> select * from test;
id | info
----+------
1 | fred
(1 row)
Notice the use of the DEFAULT keyword to insert the next available value. The same thing could be accomplished like so:
=> insert into test (info) values ('fred');
Notice also that our original table does not have any kind of unique index on the SERIAL table. While early versions of the SERIAL macro implemented a unique index automatically, that is now considered the users job to decide whether he wants such. For now, our test table will let us insert the same id more than once:
2=> insert into test (id,info) values (1,'tom');
INSERT 7322550 1
marl8412=> select * from test;
id | info
----+------
1 | fred
1 | tom
(2 rows)
For many uses this is acceptable. For most it is not. Let's recreate out table properly.
=> drop table test;
DROP TABLE
=> create table test (id serial primary key, info text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
=> insert into test (id,info) values (DEFAULT,'fred');
INSERT 7322573 1
=> insert into test (id,info) values (1,'tom');
ERROR: duplicate key violates unique constraint "test_pkey"
=> select * from test;
id | info
----+------
1 | fred
(1 row)
Now that's more like it.
What if we needed a bigint for this sequence, as it might go over 4 billion some day? Easy, we use bigserial:
=> drop table test;
DROP TABLE
=> create table test (id bigserial primary key, info text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
=> insert into test (id,info) values (DEFAULT,'fred');
INSERT 7322585 1
=> insert into test (id,info) values (DEFAULT,'tom');
INSERT 7322586 1
=> select * from test;
id | info
----+------
1 | fred
2 | tom
(2 rows)
Yes, it is that easy.
Also, should you need to, you can have more than one serial column in a table:
create table test (id bigserial primary key, id2 bigserial, info text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id"
NOTICE: CREATE TABLE will create implicit sequence "test_id2_seq" for "serial" column "test.id2"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
=> insert into test (id,id2,info) values (DEFAULT,DEFAULT,'tom');
INSERT 7322600 1
=> insert into test (id,id2,info) values (DEFAULT,DEFAULT,'dick');
INSERT 7322601 1
=> insert into test (id,id2,info) values (DEFAULT,1,'harry');
INSERT 7322602 1
=> insert into test (id,id2,info) values (DEFAULT,DEFAULT,'lou');
INSERT 7322603 1
=> select * from test;
id | id2 | info
----+-----+-------
1 | 1 | tom
2 | 2 | dick
3 | 1 | harry
4 | 3 | lou
(4 rows)
Notice the second column, id2, wasn't set unique, so I could insert the third record above with an id2 the same as the previous guy. Notice also that since the sequence generator wasn't fired that the number 3 is in the second column for lou, not 4. There is limitation here, the column could have been set as unique, but not primary key, as there can be only one of those.
Triggers can be used to force a column to update with the sequence no matter what, should one decide to not allow the application layer the freedom to control the number being inserted. Doing so is a bit beyond the scope of this tutorial. There are examples floating about on the web doing so in C, plpgsql, and a few other languages.
Remember from above that the sequence we used to insert the value was public.test_id_seq. There are three built-in functions used to manipulate a sequence, they are:
nextval('seqname')
currval('seqname') and
setval('seqname',val,[used])
They are described well enough in the documentation, but here's the short version:
nextval gets the next value and increments the sequence. It is safe for two or more connections to use it at the same time, and is guaranteed to never deliver the same number twice, unless some idiot is playing with setval.
currval gets the current value for the session you're in. In other words, if Joe connects, and Jim connects, and they each issue a nextval, Joe might get 7, and Jim would get an 8. Each one could now issue a currval, and each would get the number HE was last given. If Joe were to run nextval again in the same session, he would now get 9, since Jim got 8, and should Joe issue a currval, he would see 9, the last value delivered in his session. This function is also transactionally safe, as long as no idiot is playing with setval.
setval lets you change the value in the counter. As long as you're setting it to a higher number than it was before, you're transactionally safe. If you're setting it lower, you may be putting the number you put in there over again. If you're not careful with it, you could find yourself being referred to as the idiot that was playing with setval.
Well, that pretty much covers the basics. Hope that helps.
