Re: Am I using the SERIAL type properly? - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Am I using the SERIAL type properly?
Date
Msg-id 20030712020520.GA9380@svana.org
Whole thread Raw
In response to Am I using the SERIAL type properly?  ("Chad N. Tindel" <ctindel@falcon.csc.calpoly.edu>)
List pgsql-general
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@svana.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

Attachment

pgsql-general by date:

Previous
From: Jason Tishler
Date:
Subject: Re: PostgreSQL, Cygwin - setting locales
Next
From: Jeffrey Melloy
Date:
Subject: Re: Am I using the SERIAL type properly?