Re-initializing table sequence in postgresql

Re-initializing a table sequence is quite normal and required in some cases. I had to do that recently. If the table has data and needs to be preserved, then temporarily you have to move the data, delete the data in original table and re-initialize the sequence and copy the original data again.

Use copy column names to reinitialize key sequence.

In current versions of PostgreSQL you can specify column names with the COPY command to copy in or out only specific columns. This can come in handy if you want to recalculate your generated keys.

Given table:

CREATE TABLE table_name (
  ID SERIAL PRIMARY KEY,
  name TEXT,
  role TEXT
);

You can copy out only the name and the role like this:

  \COPY table_name (name, role) to 'something.dat'

And then you will want to delete your original data.

  DELETE FROM table_name;

Then you can reset your sequence. Perhaps you found out later that you wanted to start the id field at 100 instead of 1. To restart your sequence and copy the data back in use:

ALTER SEQUENCE table_id_seq RESTART 100;
\COPY people_131 (name, role) from 'something.dat'

This technique can be used to copy in new data if the COPY formatted data does not have the generated key info in it. In that case, you would NOT alter the sequence or delete data from your original table.

You can achieve the same effect of the original example by selecting the data into a temporary table instead of copying out. After you copy the data into the temporary table, delete the original data, reset the sequence and copy it back in.

CREATE TEMPORARY TABLE people_temp AS SELECT name, role FROM people_131;
ALTER SEQUENCE people_131_id_seq RESTART 1000;
DELETE FROM people_131;
INSERT INTO people_131 (name, role) select name, role from people_temp;

Things to notice:

• The sequence name (if you need it) is table_column_seq.
• The format of the data to copy in must match the columns speficied.

Cover photo by Adi Goldstein.