If you use PostgreSQL and have truncated a table with the hope that your primary key serial or sequence has been reset to 1, nope, it does not work that way.
How to reset the table sequence after truncating a table in PostgreSQL
Scenario
If you are coming from MySQL or MariaDB or any other database system, when you truncate a table, the primary key ID automatically gets reset with a starting ID of 1.
Not in PostgreSQL. In Postgres, the current sequence value is retained.
Assume you have a table like this:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name character varying(80 NOT NULL,
username character varying(20) UNIQUE NOT NULL,
password character varying(80) NOT NULL,
created_at timestamp DEFAULT now(),
updated_at timestamp DEFAULT now()
);
You added a million rows to it. And then, you truncated it and added a new row.
The new row starts with ID 1,000,001 instead of 1.
How will you reset the sequence numbering?
Solution: Reset the sequence after running TRUNCATE on a table
In PostgreSQL, the command to reset the sequence after truncating a table is TRUNCATE table_name RESTART IDENTITY;
In our case, we will do this:
TRUNCATE TABLE accounts RESTART IDENTITY;
After you run that command, the first insert will take an ID of 1.
Reset sequence with truncate TABLE followed by alter sequence
You can also do this in two steps, first with a regular truncate command, followed by an alter sequence command.
TRUNCATE TRUNCATE TABLE accounts;
ALTER SEQUENCE accounts_id_seq RESTART WITH 1;
How to find the sequence in PostgreSQL
If you do not know the sequence entity, \ds will show you a list of sequences.
Another way is if you know the table name, the format is TABLE_COLUMN_seq to it.
The table name is accounts, so the sequence for id would most likely be accounts_id_seq.
Conclusion
Hopefully, this worked for you. If you are unable to reset a Postgres sequence after truncating it, let me know in the comments or by email. Thanks for reading.
Related Posts
If you have any questions, please contact me at arulbOsutkNiqlzziyties@gNqmaizl.bkcom. You can also post questions in our Facebook group. Thank you.