How to fix all duplicate key value violates unique constraint
The code you display is not related to "reindexing". (Compare: REINDEX). It gets a sequence behind a serial column (or IDENTITY column in Postgres 10 or later) back in sync - by setting it to the maximum existing ID. Consider this query instead:

SELECT setval('tablename_id_seq', max(id)) FROM tablename;


No subselect needed, no off-by-one error to fix (the next value returned from the sequence is incremented by default).
Or, better yet, you don't have to provide the name of the sequence:

SELECT setval(pg_get_serial_sequence('tablename', 'id'), max(id)) FROM tablename;


Details:


How to reset postgres' primary key sequence when it falls out of sync?