Django object creation and Postgres Sequences

Question

I have an import script which runs a series of commands to get things from one Postgres DB to another, both running the same Django codebase. For the most part, it uses ./manage.py loaddata to copy over, but some objects require additional processing and I use Django's objects.create() method in a custom script to copy the data. When doing this, I specify the ID, i.e,

MyObject.objects.create(id = 2, title = 'foo')

Once the script is done, I'm noticing that the Postgres SEQUENCE is wrong on the tables where I did objects.create(). I.e., it was 50 before the import, and still 50 after, even though the table now has 150 objects. This, of course, leads to errors when creating new objects, since it tries to use an ID which already exists (On all these tables, the ID is just a vanilla auto-increment field). However, the tables which were filled via ./manage.py loaddata seem fine.

I'm aware that I can manually reset these tables with Django's ./manage.py sqlsequenreset, but I'm curious as to why the sequence seems to get out of whack in the first place. Does objects.create() not increment it? Am I overlooking something obvious?

Answer 1

everything works fine. django's create() has nothing to do with sequence incementation directly. briefly:

• postgresql auto incrementing ('serial' type) is just a shortcut of 'create sequence + create integer field with sequence value as default'
• django's autofield primary key (id integer if not specified else by you) just creates a serial field
• when you specify the id manually, postgres inserts the value into the database. when you specify a value, it omits the 'default' parameter, which is a proper behavior.

so, if you want your inserts to increment the sequence in a way of your choice, you need to manually change the sequence value using SELECT setval('sequence_name', int_value); otherwise leave it null and it will increment automatically - select the current val and increment it +1 (if not specified differently in the sequence definition).

another idea is you create the object and then update the id (of course it can't be already used) and in the end set the sequence value to the max id.

Answer 2

The autoincrement fields works, but yo must make the query like

MyObject.objects.create(title='foo')

without the id field, this is autocalculated with the database.