So, I generated a table called person from mockaroo of about 1000 rows.

      Column      |         Type          | Collation | Nullable |              Default
------------------+-----------------------+-----------+----------+------------------------------------
 id               | bigint                |           | not null | nextval('person_id_seq'::regclass)
 first_name       | character varying(100) |           | not null |
 last_name        | character varying(100) |           | not null |
 gender           | character varying(7) |           | not null |
 email            | character varying(100) |           |          |
 date_of_birth    | date                  |           | not null |
 country_of_birth | character varying(100) |           | not null |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
    "person_email_key" UNIQUE CONSTRAINT, btree (email)

Above are the table details.

I am trying to insert a row into the table. Since, I gave id as BIGSERIAL datatype, its supposed to auto increment the id for me and everytime I generate a row.

But, now as I am trying to insert a new row it’s showing me duplicate id error.

test=# INSERT INTO person (first_name, last_name, gender, email, date_of_birth, country_of_birth) VALUES ('Sean', 'Paul','Male', 'paul@gmail.com','2001-03-02','India');
ERROR:  duplicate key value violates unique constraint "person_pkey"
DETAIL:  Key (id)=(2) already exists.

>Solution :

The problem can be one of the following:

• somebody ran ALTER SEQUENCE or called the setval function to reset the sequence counter

• somebody INSERTed a row with an explicit value of 2 for id, so that the default value was overridden rather than using a sequence value

You can reduce the danger of the latter happening by using identity columns with GENERATED ALWAYS AS IDENTITY.