INSERT violating unique key constraint on primary key

on a table with primary key 'id' with default value nextval(), inserting a row throws the error

Query 1 ERROR: ERROR:  duplicate key value violates unique constraint "table_pkey"
DETAIL:  Key (id)=(6) already exists.

except this table has 200 rows. why isn't Postgres setting the new row id as 201?

You inserted rows manually into the table providing explicit values for the id column, which means the sequence was not advanced as no default value was used. Now your sequence is out of sync with the actual values in the table.

You need to sync the sequence using setval() with your actual values if you do that:

select setval( pg_get_serial_sequence('the_table', 'id'), 
               (select max(id) from the_table)
             );

If you want to prevent this kind of problem in the future, you can re-define the column as an identity column which will reject passing explicit values for it, so that you get an error if you try to bypass the default value.

StackExchange Database Administrators Q#241970, answer score: 7