Re: SQL scripts - sequences - Mailing list pgsql-general

From Brook Milligan
Subject Re: SQL scripts - sequences
Date
Msg-id 200008291628.KAA05160@biology.nmsu.edu
Whole thread Raw
In response to SQL scripts - sequences  ("Adam Lang" <aalang@rutgersinsurance.com>)
List pgsql-general
   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

pgsql-general by date:

Previous
From: Ange Michel POZZO
Date:
Subject: Re: [BUGS] Re: cannot vacuum a database !
Next
From: "Steve Wolfe"
Date:
Subject: Re: [BUGS] Re: cannot vacuum a database !