Re: Question about serial vs. int datatypes

On 05/06/2003 21:05 Lynna Landstreet wrote:

> Anyway: I'm converting a FileMaker Pro database into PostgreSQL, and have
> a
> question (actually, tons of questions, but I'll stick to one for now).
> Each
> of the three major database files in FMP (equivalent to tables in
> PostgreSQL) has a number field as a primary key which is incremented
> serially as new records are added. Nice and simple, and the same thing a
> lot
> of PostgreSQL databases use.
> 
> 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?
> 
> Any advice would be appreciated...


Looking at the docs and after a bit of playing around with 7.3.3, I think 
this may work:

1) I believe PG _will_ accept the serial numbers you insert so importing 
the data with your serial number fields should work.

2) For each serial type you will find that PG has a created a Sequence 
object with a name of the form tablename_columnname_seq. After the import, 
you will need to set this to the max value of your serial column with 
SELECT pg_catalog.setval("sequencename", value, true); 
HTH

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+