Re: Serial/sequence problem

On Tue, Nov 25, 2008 at 05:39:49PM +0930, Michael Hall wrote:
> On Tue, Nov 25, 2008 at 08:26:55AM +0100, A. Kretschmer wrote:
> > Set the sequence to the new value, 3636+9, via
> > setval('your_sequence', 3636+9)
>
> I'll re-import the data with DEFAULT in the id (SERIAL) column,
> hopefully new inserts will be OK then and I can leave the sequence
> alone?

Omitting the column is generally the easiest from the code's point
of view.  If you're specifying a value PG is interpreting this as a
statement that you'll take care of everything and it should stay out of
the way.  The only time this causes me annoyance is when I'm inserting a
new hand built dataset, e.g.

  INSERT INTO foo (fooid,val) VALUES
    (1,'hello'),
    (2,'goodbye');

  INSERT INTO bar (name,fooid) VALUES
    ('sam',1),
    ('michael',1),
    ('jack',2);

Here I prefer not to rely on the behavior of the sequences to give me
monotonically increasing values and put the values in myself.  PG then
requires a fixup call to setval, such as:

  SELECT setval('foo_fooid_seq',MAX(fooid)) FROM foo;

One nice feature of PG is its RETURNING clause.  If you put "RETURNING
fooid" on the end of the INSERT INTO foo statement you'll get the values
that PG used for this column after expanding the defaults.  This can be
useful for code when you're inserting lots of data and want to be able
to refer back to it later.


  Sam