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   |
+------------------------------+---------------------------------------------+