Unable to update Postgres because of natural primary key

[jos.car...@yahoo.com]

I'm new at SA and I run into problems with natural primary keys. These are PK's not created by a Postgres sequence, but are unique strings. I insert/upgrade data from a csv.

When the PK is new everything works fine, but when an existing record has to be updated the following error is given:

IntegrityError: (IntegrityError) duplicate key value violates unique constraint  store_item_pkey

I use the following SA code to insert/update 

    db.session.add(import)

    db.session.commit()

At first I thought there was something wrong with my SA, but after reading the SA documentation and searching on Internet, I think it has anything to do with the natural primary keys. Any advice on how to solve this?

[Gunnlaugur Briem]

Hi,

instead of db.session.add, what you want is:

import = db.session.merge(import)

See http://www.sqlalchemy.org/docs/orm/session.html#merging : "It examines the primary key of the instance. If it’s present, it attempts to load an instance with that primary key (or pulls from the local identity map"

Maybe you were confused by the heading "Adding New *or Existing* Items" in http://www.sqlalchemy.org/docs/orm/session.html#adding-new-or-existing-items ... here the "existing" part only applies to *detached* instances (ones that were previously associated with a session but have been removed), not to *transient* ones (new instances that SQLAlchemy hasn't already seen). Transient instances are assumed new by session.add, it doesn't query the database to check if the primary key exists.

See "Quickie Intro to Object States" http://www.sqlalchemy.org/docs/orm/session.html#quickie-intro-to-object-states and then the rest of the Session tutorial; that should get you going.

Regards,

- Gulli