Re: Resetting Serial Column Sequence Number

Andreas Kretschmer wrote:
> 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"

If you want to start at 1 why not
SELECT setval('locations_location_id_seq', 1);

>>> 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