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, 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.
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.
You should look up ALTER SEQUENCE
Andrew Ayers wrote:
Show quoted text
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?
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...)