Horton

Thread

  1. Re: SERIAL type fields

    Andrew Ayers <aayers@eldocomp.com> — 2003-04-24T19:43:24Z

    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

  2. Re: SERIAL type fields

    scott.marlowe <scott.marlowe@ihs.com> — 2003-04-24T19:46:04Z

    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.

  3. Re: SERIAL type fields

    Bruno Wolff III <bruno@wolff.to> — 2003-04-24T19:53:04Z

    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.

  4. Re: SERIAL type fields

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-04-24T19:59:34Z

    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

  5. Re: SERIAL type fields

    Andrew Ayers <aayers@eldocomp.com> — 2003-04-24T20:02:27Z

    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

  6. Re: SERIAL type fields

    scott.marlowe <scott.marlowe@ihs.com> — 2003-04-24T20:21:53Z

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