I recently had the unfortunate task at work of copying most of a production PostgreSQL database to a development server. The whole database would have been simple. It’s the “most of” part that made things messy. At any rate, I ended up in a position where I needed to make various incorrect “sequence” definitions match their respective tables. For example, the maximum id in table principals was 18133 and the sequence used for that primary key was currently at, well, 1.
I’d never ever delved into this sort of thing before (I had to look up what a PostgreSQL sequence was) as I’m not a DBA by trade. I also won’t likely have to do this again for quite some time, so here are my notes:
Query the last value for the sequence (well call this SEQLAST below, and assume it returned 3):
SELECT last_value FROM mytable_sequence;
Query for the largest id (our primary key, we’ll call this LARGESTID below and assume it returned 18133):
SELECT id FROM mytable ORDER BY id DESC;
If SEQLAST and LARGESTID match, you’re all set. If they don’t, you need to adjust mytable_sequence so that the next primary key issued is LARGESTID + 1 for mytable. You do this with the setval sequence function.
SELECT setval('mytable_sequence', 18133);
This reads as, “Set the last value of mytable_sequence to 18133 and increment before handing out the next number when nextval() is called for this sequence.” The incrementing is implied, though you could also specify the clearer and functionally equivalent statement via:
SELECT setval('mytable_sequence', 18133, true);
You could also specify “Set the next value of mytable_sequence to 18134 and do not increment before handing out the next number when nextval() is called for this sequence.”
SELECT setval('mytable_sequence', 18134, false);
Reference: Google Search: site:postgresql.org postgresql setval
