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 Oct 27 '16 at 08:54 — Burhan Khalid, Oct 27 '16 at 08:54

score 9 · Answer 1 · edited May 23 '17 at 12:10

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 '17 at 12:10

Community

1
1

answered Oct 27 '16 at 10:07

amrx

673
1
9
23

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))` – Ian Dennis Miller Dec 18 '17 at 14:22

score 7 · Answer 2 · answered Oct 27 '16 at 09:19

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 '16 at 09:19

eshwar m

184
4

2

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. – amrx Oct 27 '16 at 09:25
The explanation given by patricus here would help you understand [http://stackoverflow.com/questions/37970743/unique-violation-7-error-duplicate-key-value-violates-unique-constraint-users] – eshwar m Oct 27 '16 at 09:31
Thanks. I'd manually inserted some records with id. That was a mistake. – Martlark Jul 04 '19 at 05:00

Postgres SQLAlchemy auto increment not working

2 Answers2

Linked