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

Are you committing the transaction at the right time?

Burhan Khalid
– Burhan Khalid

2016-10-27 08:54:29 +00:00
Commented Oct 27, 2016 at 8:54 — Burhan Khalid
– Burhan Khalid, Commented Oct 27, 2016 at 8:54

Community · Accepted Answer · 2017-05-23 12:10:02Z

9

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

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Oct 27, 2016 at 10:07

amrx

7131 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Dennis Miller Over a year ago

Nice catch! The following is a python-sql solution based on your link: db.engine.execute("SELECT setval('{0}_id_seq', max(id)+1) FROM fund_moment;".format(table_name))

eshwar m · Accepted Answer · 2016-10-27 09:19:40Z

7

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

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

answered Oct 27, 2016 at 9:19

eshwar m

1844 bronze badges

3 Comments

amrx Over a year ago

But the auto-increment is supposed to work with Integer type and primary key combo. Using setval where -ever the transaction takes place, hardly seems to be an efficient solution.

eshwar m Over a year ago

The explanation given by patricus here would help you understand [stackoverflow.com/questions/37970743/…

Martlark Over a year ago

Thanks. I'd manually inserted some records with id. That was a mistake.

Collectives™ on Stack Overflow

Postgres SQLAlchemy auto increment not working

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related