CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
amrx
amrx

Reputation: 713

Postgres SQLAlchemy auto increment not working

I have a model class :

class User(PBase):
   __tablename__ = "users"
   id = Column(Integer, primary_key=True)
   name = Column(String, nullable=False, unique=True)

Now as per the documentation , when type Integer is used along with primary_key , a sequence is generated automatically. Here is the output table 

  id      | integer           | not null default nextval('users_id_seq'::regclass)

As you can see a default sequence is generated in the modifiers column.

But when I try to add the second user, I get integrity error on primary key constraint.

IntegrityError) duplicate key value violates unique constraint   "users_pkey"
DETAIL:  Key (id)=(1) already exists.

What is wrong here?

Edit: Code for adding the user, a snap shot 

  def create(name, email, roleid)

       with self._session_context() as session:
           user = User(name, email, roleid)
           session.add(user)
           session.commit()

Upvotes: 7

Views: 8455

Answers (2)

amrx
amrx

Reputation: 713

So, figured out and answering here, so it may help others. So with Postgres if you happen to supply the id field when you insert a new record, the sequence of the table is not used. Upon further insertion if you don't specify the id, the sequence table is not used and hence you have duplication. In my app few records where default loaded from a JSON file and id was specified for these records, but for all non default values no id was supplied during insertion. This helped me

Upvotes: 9

eshwar m
eshwar m

Reputation: 184

It can be solved by issuing the following query on your database.

SELECT setval('users_id_seq', MAX(id)) FROM users;

Upvotes: 7

Related Questions