Postgres primary_key of the table - Unique Constrain Error

Question

I installed postgresql - docker[postgres:9.6.24] and manually saved some rows on the table, lets say, I added 10 rows.

When I used sqlalchemy to insert new rows on the table, I got this error

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "bird_pkey" DETAIL: Key (bird_id)=(1) already exists.

Code -

session = self.Session()
bird_data = Bird(name='Parrot', population=50)
session.add(vehicle)
session.commit()

Table -

class Bird(Base):
    __tablename__ = 'bird'
    bird_id = Column(Integer, primary_key=True)
    name = Column(String)
    population = Column(Integer)

But, there are 10 records already saved in the Bird table. I don't why the primary key is showing the unique constrain error as it is usually calculate the primary key value automatically for the new records.

How to solve this

I tried this answer, but I got the syntax error

create sequence bird_seq increment 1 start 11;
SELECT nextval('bird_seq');
select setval('bird_seq', (select max(bird_id) from bird)+1);

but this is also not worked, I got the same error, but this time the bird_id is assigned to 2

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "bird_pkey" DETAIL: Key (bird_id)=(2) already exists.

How to solve this?

The primary_key for this table is not properly assigned, Now I am wondering that, this issue will cause error for all other tables when inserting new records? If so, How to solve this issue for all tables in the database

Thanks

score 0 · Answer 1 · answered Jun 15 '23 at 13:20

You created a table, and inserted data manually, you say and there was probably no primary key defined then. Then defined the table with SQLAlchemy which did the job properly and inserted a sequence and primary key flag on your column. Now when you try to insert data it fails because the SQLAlchemy primary key sequence starts at 1 and you already have rows with this bird_id

The easiest thing to do would be to wipe out the data you inserted manually.

If you are reluctant to do this for some reason, then you need to find the name of the sequence created by SQLAlchemy, and increment that. The fact that you created a new sequence and incremented it does nothing if it is not the sequence associated with the primary key for this table.

Postgres primary_key of the table - Unique Constrain Error

1 Answers1