Duplicate key value violates unique constraint

I have setup a table like so

CREATE TABLE teams
(
  ID SERIAL PRIMARY KEY,
  league_name VARCHAR,
);

When running an insert I get the error duplicate key value violates unique constraint "teams_pkey"

My insert looks like

async function saveMissingTeamData(teamObject) {
  let response;
  try {
    response = await pool.query('INSERT INTO teams (league_name) VALUES ($1) RETURNING *', [teamObject.league_name]);
  } catch (e) {
    console.error('Error Occurred in saveMissingTeamData', e);
    throw e;
  }
  return response.rows;
}

I was under the impression (could be wrong) that by specifying SERIAL then auto incrementation of the id would be handled by psql?

What's strange is that it will work on my production environment but not on my staging environment (which is a copy of lives data)

Any thoughts on how I can rectify this please?

CodePudding user response：

The problem is that, for some hard-to-discover reason, the sequence associated with the column id does not match the actual contents of the column. You can fix it with this one-time intervention:

select setval('teams_id_seq', coalesce((select id  1 from teams order by id desc limit 1), 1), false);

Read about Sequence Manipulation Functions.