Before we reset the sequence, let us make sure that it is out of sync.
SELECT nextval(PG_GET_SERIAL_SEQUENCE('"table"', 'id'));
SELECT
CURRVAL(PG_GET_SERIAL_SEQUENCE('"table"', 'id')) AS "Current Value",
MAX("id") AS "Max Value"
FROM "table";
Line #1: We make a call to nextval because if we call currval before calling nextval in the current session, it will give us an error similar to "ERROR: currval of sequence "table_id_seq" is not yet defined in this session".
Line #3-6: Gets the value most recently obtained by nextval for this sequence in the current session along with the maximum value for id in table.
Our sequence is out-of-sync when the Current Value is less than Max Value.
What does PG_GET_SERIAL_SEQUENCE do?
PG_GET_SERIAL_SEQUENCE('"table"', 'id') can be used to get the sequence name.
Using this, we can avoid hard-coding the actual sequence name, which helps prevent incorrect assumptions about the sequence name.
Note that the table name is in double-quotes, surrounded by single quotes.
The Fix
SELECT SETVAL(
(SELECT PG_GET_SERIAL_SEQUENCE('"table"', 'id')),
(SELECT (MAX("id") + 1) FROM "table"),
FALSE);
This will set the sequence to the next available value higher than any existing value for "id" in table. Further inserts will not result in a "duplicate key value violates unique constraint" error.