Postgres SQLAlchemy auto increment not working

Question

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()

Answer 1

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

Answer 2

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

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