how to best resync serial columns

On Nov 10, 2008, at 6:48 PM, Brent Wood wrote:

Hi,

I have a number of tables with serial columns as a primary key.

I'm looking to add lots of records via copy, but should reset the
serial counters to the appropriate value after this.

Is there a simple way to do this, or do I just update the last_value
column in each seq table to the max(id) from the relevant table.

You shouldn't edit sequence table directly. To set a sequence's value
you should use the setval(seqname, seqval) function like so:

SELECT setval('some_seq', 1000);

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Thanks Erik...

I found an alternative to psql copy to stdout | psql copy from stdout.

I used pg_dump -n schema | psql

This approach replicated the entire schema, rather than just the table contents,
into the new database, and therefore copied over all the seq data as well. It
worked well in this situation.

Thanks for the reply, I'll note it for future reference.

Cheers,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

Erik Jones <ejones@engineyard.com> 11/11/08 8:03 PM >>>

On Nov 10, 2008, at 6:48 PM, Brent Wood wrote:

Hi,

I have a number of tables with serial columns as a primary key.

I'm looking to add lots of records via copy, but should reset the
serial counters to the appropriate value after this.

Is there a simple way to do this, or do I just update the last_value
column in each seq table to the max(id) from the relevant table.

You shouldn't edit sequence table directly. To set a sequence's value
you should use the setval(seqname, seqval) function like so:

SELECT setval('some_seq', 1000);

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.