Re: Question about serial vs. int datatypes

On Thu, Jun 05, 2003 at 04:05:53PM -0400, Lynna Landstreet wrote:
> But... when converting an existing database that already has several hundred
> records in it, I can't make that field serial in PostgreSQL, can I? Because
> I don't want the existing records renumbered - that would break the links
> between the different tables. But if I make the id number just a smallint
> field, then I'm stuck incrementing it manually after the conversion. Is
> there any way around this? Any way to import the existing records with their
> id number intact, and then have it switch to serial after that for new
> records?

Make a new table with a serial type field and then do a \d on that table. You will see that the default value for the field is the next value from the relevant (autocreated) sequence. If you specify a value for the "SERIAL" type field, the field will be filled with that rather than a value from the sequence.

Note that if you do this you need to reset the value of the current sequence. Use something like setval(sequencename) to max(your_serial_field).

Rory