Thread: Re: SERIAL type fields
All, I have been doing a conversion of an Access 97 database to PostgreSQL - so far things have been going relatively smoothly. However, I am now running into a minor issue - one could almost say it is an asthetic issue more than anything. In the conversion of my Access tables, to a number of them I had to add a unique key field. I decided to use a SERIAL type for these fields, which set up a "sequence" table (table_field_seq) for each table that uses one. I am now in a process of doing a multiple conversion of data to these new tables, where I clear out the table then reload it with new data. What I came across was that I wanted to reset the sequence number so that when the new records were added, they would start at number "1" and increase from there. When you create the table from new, it does this - but after that, the sequence table says that the minimum value for the sequence is "1" - and not zero - so that when you add records to the table the sequence is used on, the first record has a value of "2" in that field when it is added. I tried to reset the minimum value to "0" - but it wouldn't let me. Does anyone know of how you do this, without having to DROP the table and sequence, and re-creating them? Is there some kind of ALTER TABLE command, or possibly something the database setup, that would allow me to alter this behavior? Thank you for any answers you can provide... Andrew L. Ayers
On Thu, Apr 24, 2003 at 12:43:24 -0700, Andrew Ayers <aayers@eldocomp.com> wrote: > > What I came across was that I wanted to reset the sequence number so > that when the new records were added, they would start at number "1" and > increase from there. If you look at the documentation for setval, you will see that there is an optional third argument that you can use to keep the value from being incremented on the next nextval call.
On Thu, 24 Apr 2003, Andrew Ayers wrote: > When you create the table from new, it does this - but after that, the > sequence table says that the minimum value for the sequence is "1" - and > not zero - so that when you add records to the table the sequence is > used on, the first record has a value of "2" in that field when it is added. > > I tried to reset the minimum value to "0" - but it wouldn't let me. Does > anyone know of how you do this, without having to DROP the table and > sequence, and re-creating them? Is there some kind of ALTER TABLE > command, or possibly something the database setup, that would allow me > to alter this behavior? Sure, take a look here: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-sequence.html Notice near the bottom, you get this: setval Reset the sequence object's counter value. The two-parameter form sets the sequence's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value. In the three-parameter form, is_called may be set either true or false. If it's set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. For example, SELECT setval('foo', 42); Next nextval() will return 43 SELECT setval('foo', 42, true); Same as above SELECT setval('foo', 42, false); Next nextval() will return 42 That last one is what you want.
You should look up ALTER SEQUENCE Andrew Ayers wrote: > > All, > > I have been doing a conversion of an Access 97 database to PostgreSQL - > so far things have been going relatively smoothly. However, I am now > running into a minor issue - one could almost say it is an asthetic > issue more than anything. > > In the conversion of my Access tables, to a number of them I had to add > a unique key field. I decided to use a SERIAL type for these fields, > which set up a "sequence" table (table_field_seq) for each table that > uses one. > > I am now in a process of doing a multiple conversion of data to these > new tables, where I clear out the table then reload it with new data. > > What I came across was that I wanted to reset the sequence number so > that when the new records were added, they would start at number "1" and > increase from there. > > When you create the table from new, it does this - but after that, the > sequence table says that the minimum value for the sequence is "1" - and > not zero - so that when you add records to the table the sequence is > used on, the first record has a value of "2" in that field when it is added. > > I tried to reset the minimum value to "0" - but it wouldn't let me. Does > anyone know of how you do this, without having to DROP the table and > sequence, and re-creating them? Is there some kind of ALTER TABLE > command, or possibly something the database setup, that would allow me > to alter this behavior? > > Thank you for any answers you can provide... > > Andrew L. Ayers > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
scott.marlowe wrote: > Sure, take a look here: > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-sequence.html > > Notice near the bottom, you get this: > > setval [snip] So - it seems like yet another case of RTFM... Thank you, everyone... Andrew
On Thu, 24 Apr 2003, Andrew Ayers wrote: > scott.marlowe wrote: > > Sure, take a look here: > > > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-sequence.html > > > > Notice near the bottom, you get this: > > > > setval > > [snip] > > So - it seems like yet another case of RTFM... Reading the FM in Postgresql is pretty easy, but sometimes finding the right place isn't so easy. Like how to set environmental vars that affect psql (hint, it uses libpq...)