Re: Resetting Serial Column Sequence Number

Andreas Kretschmer <akretschmer(at)spamfence(dot)net> schrieb:

> Adam <adam(at)spatialsystems(dot)org> schrieb:
>
> >
> > I just emptied my table and I want all my new inserts to start with a
> > 'location_id' of '1'. The table is named "locations" with a SERIAL column
> > "location_id"
> >
> > I tried the below SQL to rest the sequence ID but it's not working. What am I
> > doing wrong?
> >
> > SELECT setval('locations_location_id_seq', (SELECT max(location_id) + 1 FROM
> > locations));
>
> The table locations are empty? Yeah, select max(location_id) from an
> empty table is NULL, not 0. And NULL+1 -> NULL. You can't set a Sequence
> to NULL, that makes no sense.

Btw, to avoid this, you can use coalesce():
SELECT setval('locations_location_id_seq', (SELECT coalesce(max(location_id),0) + 1 FROM locations));

coalesce returns the first non-null value, either the result from max()
or the second parameter, 0.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°