RE: Serial field - Mailing list pgsql-novice

I actually was torn in my interpretation between the
question that I answered and the one that you
answered.  Since I wasn't sure what the problem was I
chose to answer the question that I thought most
interesting.

If you are correct and Francois simply needs to be
able to import data from another source and "then"
have future values auto increment then that is also
quite possible.  In fact, if you used versions of
PostgreSQL before the SERIAL type was available you
would already know the answer on how to import data
that already has information in the field that you
want to have become auto increment field.

The secret lies in how the SERIAL type is implemented
in PostgreSQL.  For example if I were to create the
table orgs like this:

processdata=> CREATE TABLE orgs (id serial, name
char(10));
NOTICE:  CREATE TABLE will create implicit sequence
'orgs_id_seq' for SERIAL column 'orgs.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit
index 'orgs_id_key' for table 'orgs'
CREATE

You will notice that the command also creates a
sequence orgs_id_seq.  This sequence is where orgs
gets its values for orgs.id.  Once you know how the
serial type actually works it becomes straightforward
to simply create the table like:

processdata=> CREATE TABLE orgs (id int primary key,
name char(10));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'orgs_pkey' for table 'orgs'
CREATE

Once the table is created you can easily import your
data in whatever manner makes you the happiest.  Once
your data is imported it is a simple manner to find
out the largest value for orgs.id (or whatever) with a
select statement like this:

SELECT max(id) FROM orgs;

Then create a new sequence with a start value one
higher than the value that is returned:

CREATE SEQUENCE orgs_id_seq START <value>;

Once you have got a sequence then you simply alter the
table so that it gets it's default values from that
sequence:

alter table orgs alter id set default
nextval('orgs_id_seq');

Presto, you have just created an auto increment field
from the ground up.

Hope this is helpful,
Jason

--- Robby Slaughter <webmaster@robbyslaughter.com>
wrote:
> Jason and Francois:
>
> Jason: That's not how I interepreted Francois'
> request,
> but it's  a clever approach to what sounds like a
> common
> need in database design. And yes, using a recent
> version
> of PostgreSQL is a good idea!
>
> Francois: It sounds like you just want an
> auto-increment field but you don't want to
> values you've already have in the table.
>
> I think your best option is to do create the table
> first just using standard INTEGERs and import your
> data into the table. Then do an ALTER TABLE and
> change the table design.
>
> I don't have a convienent way to try it right now
> but that should work.
>
> If it doesn't, you count always just create your
> own pgplsql stored procedure that works like
> a standard sequence. As far as I can tell, when
> you create a SERIAL type, that's what happens
> anyway. The table definition ends up having
> a DEFAULT value for the serial field, which
> is a function call to
> nextval('tablename_fieldname_seq');
>
> You could implement the same code yourself and just
> not "turn on" the function until after your original
> data is entered.
>
> Hope that helps.
>
> -Robby
>
> > I want to migrate a database to PostgreSQL 6.5.2
> > I need an auto-increment field. I first tried the
> > SERIAL type, but it
> > doesn't fit my needs.
> > In fact, I want to:
> > 1/ keep the original INT value of my existing
> > records from another server
> > (say, first row can be "1", second "3", next one
> > "17",...)
> > 2/ have an automatic incrementation of the last
> > value for a new record. For
> > exemple, with the values above and a SERIAL field,
> > the default value for a
> > new field would be "1" (first use of the sequence)
> > instead of "18" (last
> > value+1)..
> > I hope my english is not too obscure !
> > Any advice will be welcome
> > Regards
> >
> > --
> > Frangois THOMAS
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
>
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail
> http://personal.mail.yahoo.com/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/