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 <[EMAIL PROTECTED]> 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
pgp00000.pgp
Description: PGP signature
