postgres autoincrement

[nightowl]

how can I use an autoincrement field in postgres ?

How should I design that field and how should an insert-command look like ?

[bfranco]

First of all, you create a sequence:
CREATE SEQUENCE people_seq;

Then you create the table:
CREATE TABLE people (
id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('people_seq'),
first_name VARCHAR(30),
last_name VARCHAR(30)
);

And finally, you insert the data:
INSERT INTO people(first_name, lastname) VALUES ('joe', 'doe');
INSERT INTO people(first_name, lastname) VALUES ('john', 'smith');

You can declare the id field as SERIAL (instead of INTEGER ...), and PostgreSQL automates the creation of the sequence and some other things, but I like doing it this way.

[[deleted]]

I'd definately recommend creating teh sequence manually too, because you get to define the many of the sequence.

The next question you are bound to ask is: how can I get the value that was generated when I insert a record?
The answer is a little tricky. It's probably better to leave the default value out of the table definition, and put the value in manually. You'd first run a query to get the next value from the sequence, and then run an insert query where you insert the data into the record, including the value you just got from the sequence.

[Anon]

Dear nightowl:

CREATE TABLE people (
first_name VARCHAR(30),
last_name VARCHAR(30),
id SERIAL,

primary key(id));

insert the data:
INSERT INTO people VALUES ('joe', 'doe');

i modify Francois code, i think this look more easy.

bye...

ahchew1413

[nightowl]

Sorry, I don't understand this reason :
"get to define the many of the sequence" ?

Another question : what would that query look like, just to determine the next sequence-val ?

And a remark (it might be a stupid one): what if I use your trick, I ask for the next id and before I can insert the record, another script/user asks for it too, it will probably return the same "next value" which might cause an error if I try to insert that second record?

[bfranco]

1)I'm sure vincent is talking about all the things you can specify in the CREATE SEQUENCE sentence:

CREATE SEQUENCE seqname [ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE ]

Here is the description:

http://www.postgresql.org/idocs/index.php?sql-createsequence.html

2)Sequences ALWAYS give unique values, even to concurrent transactions.

[[deleted]]

1) actually I tried to type the word 'name', but my fingers were thinking of women again ;-)

[Anon]

This can cause ugly race conditions that result in incoherency issues in your data set.

Always do it with real sequences, not pseudo sequences you prorgram youself. If programmed wrong, they can corrupt your data, and at their best they do not handle race condistions gracefully, i.e. one insert fails for no real good reason.

for example, the following table:

create table yada (issues text, id serial);

with the following script:

pg_exec($conn,"begin");
$res = pg_exec($conn,"insert into yada values ('$issue'");
$oid = pg_getlastoid($res);
$res2 = pg_exec($conn,"select id from yada where oid=$oid");
$id = pg_result($res2,0,id);
$res3 = pg_exec($conn,"insert into anothertable values ('somedata',$id)");
pg_exec($conn,"end");

can be run by 100 concurrent users and they all will get their insert to run.

If you do it your way there is a real possibility of a race condition that can result in rows not being inserted properly.

pg_exec($conn,"begin");
$res = pg_exec($conn,"select max(id)+1 as id from yada");
$id = pg_result($res,0,id);
$res2 = pg_exec($conn,"insert into yada values ('$data',$id)");
$res3 = pg_exec($conn,"insert into anothertable values ('somedata',$id)");
pg_exec($conn,"end");

If you don't do it in a transaction block, then 100 concurrent users running this script will result in 99 incorrectly inserted records, whereas my example would just run slower without transactional blocking, but would still run.

The first rule of writing database apps is to always ask yourself "what would happen if two users did this at the exact same time?" and write code that can handle that situation.

[bfranco]

mmmhhh... I guess that you missunderstood what vincent said...

He wrote:
'You'd first run a query to get the next value from the sequence' --->SELECT nextval('sequence_name');

'and then run an insert query where you insert the data into the record, including the value you just got from the sequence.' --->INSERT INTO table_name(id_field, field_1, field2, ...) VALUES (value_from_the_sequence, value1, value2, ...)

There's no danger at all here, it's perfectly safe.

[[deleted]]

Whoa there scott, I think you may want to take cup of coffee and rethink that post :-)

I guess I didn't make myself clear (I do that sometimes)

My suggestion is to create a normal SEQUENCE, and query that sequence to get a new value for use in any new records you are inserting.
This is perfectly safe because a sequence will never give the same number twice.
So no matter how many concurrent inserts are going on, none of them will get the same number from the sequence.

like this:

$res = pg_exec($conn,"select nextval('seq_id')");
$id = pg_result($res,0,id);
$res2 = pg_exec($conn,"insert into yada values ('$data',$id)");
$res3 = pg_exec($conn,"insert into anothertable values ('somedata',$id)");

[[deleted]]

Like francois said, it is better to create the sequence manually, and not rely on the database to fix things for you.

[[deleted]]

Dang, I pressed 'submit' three minutes too late :-)

[Anon]

Oh, ok. Nevermind. :-)

my mistake.

[Anon]

I'm not sure it's better to do it yourself unless you really understand the implace of what changes you are making to a default sequence built by the serial pseudo type.

How many times have we seen people on phpbuilder asking how to convert a mysql sequence based on tinyint into something else because they didn't realize a tinyint (8 bits) was only good for 256 numbers?

A neat trick is to build a table with the serial keyword, then use pg_dump to examine how postgresql's built-in serial method did it.

Also, you can drop the table and sequence, and edit the pg_dump file to make changes as well.

[[deleted]]

I'm not that worried about 'newbies' messing up with the sequence, PostgreSQL doesn't allow you to assign a datatype to a sequence, it's always int4.

I agree about the pg_dump thing though, good way to learn how it's done if you don't understand the manual.

[nightowl]

Phew, looks like I started quite a thread. Thx all for the help about this, I think I understood 🙂

Thought there was a FAQ about this one on Vincent's page, though I couldn't find it.

[pedrotuga]

ok... so what should i use as min man and increment?

should i use a deafault sequence like:

create sequence seq1;

?

[Sxooter]

The advantage of using the serial type is that it is created as a dependent object to the table, and when you drop your table the database will drop the sequence for you. Note that it also prevents you from accidentally deleting your sequence behind the integer type as well.

Generally speaking, for 90% or more of the uses of an autoinc field, serial is just fine. You'll know when you need to do it by hand.

Also, there's bigserial for when 2³¹ values just isn't enough (2⁶³ should do though)

I'd suggest reading the very excellent PostgreSQL docs on serial here:

serial docs

[sdsd69]

Thank You bfranco!
This helps me a lot.
You are awesome.
Keep it up!!!