The problem I am encountering is with having a primary key labeled as
serial. Do I have to drop the sequence also to be able to recreate the
table? Omitting the sequence drop did not seem to allow the creation of the
table with a serial, but when I added a drop sequence, the table creates
fine in the script. Am I assuming correctly?
Yes and yes.
If that is correct, what if I go a step farther and have data dumped from
the table before it is dropped. Won't my sequence be screwed up if I drop
it and then reimport the data? Would the sequence technically be back at 1
and if I have 100 records, will it try to insert duplicate data?
If that is true, what would be the solution? Create the sequence manually,
don't drop the sequence in the script, and have the create table script not
as a SERIAL, but have it set to a default value of the sequence?
My solution in a similar situation is to have a bunch of scripts to
drop/create the tables/functions/views/triggers/... needed. Use
pg_dump to dump just the data (not the schema) but include the -c flag
(I use -a -c -D) so that sequences are dropped and recreated with the
right values. To reload: run pg_dump -a -c -D, run your scripts
(which drop and recreate the data structures, then run your pg_dump
output through psql to reload the data. Sequences and tables will
agree fine.
Cheers,
Brook