Postgres manually alter sequence

Specifying a sequence value precisely

Sometimes, you may want to alter a sequence with surgical precision. The setval function is the scalpel in this procedure:

SELECT setval('sequence_name', desired_value, should_increment);


These are the parameters:

• sequence_name is where the sequence lives (like a hermit).
• desired_value is the new value to set (like a monarch's decree).
• should_increment is a boolean - should the sequence increment after the next call?

Example:

-- Postgres is my buddy. I think I'll ship 21 payments by owl post today.
SELECT setval('payments_id_seq', 21);


And the same action but with an increment:

-- Great Scott! We need to accelerate!
SELECT setval('payments_id_seq', 21, true);


Dealing with unsteady sequences

Let's handle sequences in more dynamic databases. Picture this: the sequences leap around tables like an electron on an atom. You are the quantum physicist who ensures stability with these coding nucleotides:

Safely reset sequence

-- Be the ninja warrior of sequence resets, slice the cake at the right spot!
SELECT setval(pg_get_serial_sequence('table_name', 'id'), COALESCE(max("id"), 0) + 1, false) FROM table_name;


This command guarantees your sequence starts at the id after the highest in the sequence list.

Finding and resetting all sequences

In need to find all sequences hiding in the database?

-- Say "Hello" to your many sequence buddies!
SELECT * FROM information_schema.sequences;


For the resetting ceremony, you can create a temporary script (like a genie in a bottle):

psql -Atq -f reset.sql -o temp


And release the genie:

psql -f temp


Common pitfalls & resurrection potions

Don't use setval(unknown) or ALTER SEQUENCE LASTVALUE because that's similar to arguing that Schrödinger's cat is a unicorn - wrong on so many levels.

When greeted with typed variables, cast them properly:

-- Give the word, perform the spell!
SELECT setval(my_text_variable::regclass, my_other_variable::bigint);


Tips, tricks & magical hats

Database performance and efficiency

Let's make setval a master of database command:

SELECT setval('sequence_name', (SELECT MAX(column) FROM table_name) + 1, false);


Survivor's guide to faulty sequences

Strictly avoid setting the sequence to an existing table value: billiard balls can't occupy the same space (thankfully!).

The age ordeal

Feeling time pinched? The START WITH command will loosen the knot, but only with PostgreSQL 8.4 or later.

Reference game strong

When playing crosswords with PostgreSQL commands, the official documentation is your best partner.

Script startups

Avail sequence correction scripts from PostgreSQL wiki to tame the beast in batch resets.