Обсуждение: Serial/sequence problem

I have just imported 3636 rows into a PG database table (PG 8.1 on CentOS 5.2 ... the default).
The rows were imported using separate INSERT statements for each row. All OK so far.

After having had a few attempts at inserting new test rows (which all inserted OK), I notice that the last_value count
inthe sequence for this table is only 9. I was expecting something above 3636 of course. So now I'm getting duplicate
serialnumbers in my SERIAL id field. 

It appears to be not possible to manually update the last_count value in the sequence table (currently 9, though the
tablecontains 3636+9 rows), so I'm wondering: 

- what did I do wrong to arrive in this situation?
- how can I rectify the situation?

Thanks

Mick

am  Tue, dem 25.11.2008, um 16:41:43 +0930 mailte Mike Hall folgendes:
> I have just imported 3636 rows into a PG database table (PG 8.1 on CentOS 5.2 ... the default).
> The rows were imported using separate INSERT statements for each row. All OK so far.
>
> After having had a few attempts at inserting new test rows (which all
> inserted OK), I notice that the last_value count in the sequence for
> this table is only 9. I was expecting something above 3636 of course.
> So now I'm getting duplicate serial numbers in my SERIAL id field.
>
> It appears to be not possible to manually update the last_count value
> in the sequence table (currently 9, though the table contains 3636+9
> rows), so I'm wondering:
>
> - what did I do wrong to arrive in this situation?

The 3636 INSERT-Statements contains a fix value for the ID-Column,
right? Thats wrong, omit the id-column and value or use simply 'default' for it.


> - how can I rectify the situation?

Set the sequence to the new value, 3636+9, via
setval('your_sequence', 3636+9)



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

> The 3636 INSERT-Statements contains a fix value for the ID-Column,
> right? Thats wrong, omit the id-column and value or use simply 'default' for it.

That's right. I thought that might be the case.

> > - how can I rectify the situation?

> 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
thesequence alone? 

Thanks

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