"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> On: Fri, 21 Mar 2008 13:53:36 -0500, "Adam Rich" <adam.r@sbcglobal.net>
> wrote:
>> I'd like to point out that using pg_dump does in fact apply sequences
>> to columns after the fact. (at least in 8.3) Columns lose their
>> "serial" designation after each backup/restore (and therefore during
>> version upgrades)
> Can someone expand upon this observation with respect to tables with surrogate
> primary keys generated by a sequence?
The short answer is that Adam's statement is wrong, or at least
misleading. pg_dump does restore serial columns to the same state
they were in. (This has little to do with whether the word "serial"
is used in the dump script, which is what he seems to be complaining
about.)
There are some corner cases involving altering either the name of the
serial column or the name of the associated sequence (but not both
to match) that a dump and reload wouldn't exactly reproduce in pre-8.2
versions.
regards, tom lane