2

In Django 1.8.11, using a PostgreSQL backend, if I have a table with 3 items in it (id of 1, 2, 3), and I insert an item manually like so, specifying the id explicitly:

i = MyItem(id = 10, field1 = "foo")
i.save()

I note that the next value of the table's AutoField sequence doesn't update:

>> select nextval('myapp_myitem_id_seq')
4

Is this by design? I see this question that tells me how to reset the sequence, but why should the sequence fall out of sync in the first place? Shouldn't the sequence be set to 11 in this example?

Community
  • 1
  • 1
Jonah Bishop
  • 12,279
  • 6
  • 49
  • 74
  • Why do you need to set the `id` manually? – ahmed Apr 07 '16 at 20:24
  • I'm moving data from one table to another (long story), and I want all columns to retain their exact values. Everything works fine, but the internal sequence counter doesn't get updated, so future adds throw errors when there's a key conflict. I would have assumed the sequence would be updated properly. – Jonah Bishop Apr 07 '16 at 20:25
  • ok, then the id should be unique if you copy from a table with unique ids, i guess you need to reset the conter manually using pg command line. Also the `get_or_create()` could be handy. – ahmed Apr 07 '16 at 20:30

1 Answers1

2

It is indeed by design. A sequence is just another object in the database that is separate from the table. It is really just what it says – a way for the database to generate an arithmetic sequence of numbers. It can be linked to a table, and is very often used to generate ID values automatically, but that is not the only thing it can be used for. (Note that this is different from the way some other databases handle AutoFields, such as MySQL's AUTO_INCREMENT flag. AUTO_INCREMENT can only be used for the ID field, while a sequence is more general. Yeah, and other databases still will completely ignore any values you specify for the ID field, unless you explicitly ask the database not to ignore the value beforehand – hello, MS SQL, I'm talking about you.)

Whenever you need to import existing data into your database, including auto-ID values, you have to make sure to update the sequences associated to all relevant tables afterwards. There is no magic that would do this automatically on every INSERT statement, at least not by default. (I imagine you could achieve something to that effect using a trigger, but I've never tried anything like that.)

By the way, the PostgreSQL wiki contains a very useful recipe to fix all sequences in a database, which I didn't see referenced in the question you linked (at least not at a quick glance).

koniiiik
  • 4,240
  • 1
  • 23
  • 28