Re: Resetting Serial Column Sequence Number
От | Andreas Kretschmer |
---|---|
Тема | Re: Resetting Serial Column Sequence Number |
Дата | |
Msg-id | 20061014065255.GA10502@KanotixBox обсуждение исходный текст |
Ответ на | Re: Resetting Serial Column Sequence Number (Andreas Kretschmer <akretschmer@spamfence.net>) |
Ответы |
Re: Resetting Serial Column Sequence Number
|
Список | pgsql-general |
Andreas Kretschmer <akretschmer@spamfence.net> schrieb: > Adam <adam@spatialsystems.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°
В списке pgsql-general по дате отправления: