-1

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.
Sejal Dahake
  • 1
  • 3

1 Answers1

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263