Re: Am I using the SERIAL type properly?

On Thu, Jul 10, 2003 at 12:10:31AM -0700, Chad N. Tindel wrote:
> I tried doing numerous searches to see if this has been discussed before,
> but I couldn't find anything. If it has, perhaps my question can be
> answered simply by a pointer to a previous thread.
>
> I'm trying to convert a program from mysql to postgres (yes, I've seen the
> light). I'm running into some problems though. From everything I've
> read, the SERIAL type is supposed to be like using an auto_increment in
> mysql. However, when I try to mix and match user assigned ids (my serial
> column) with database generated ids things break.

The serial fields is only a default so the counter is only advanced when you
don't specify a specific value in the insert. So in your first example, the
first four insert did not move the serial counter from 1 so the last tried
to insert keys 1 and 2 which failed because it's a primary key.

You second example works because you're explicitly calling nextval(). The
insert calls it too so you skip values.

Solutions are:
- Don't include the serial field in the insert statement
- Specify the value DEFAULT instead of an actual value in the inserts
- Specify nextval() explicitly in your inserts
- Create a trigger so it overrides any value you put in with the next serial

Note, you can't really avoid holes in the sequence. This is a FAQ somewhere.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington