# Issues importing fixtures with id=0 and postgres backend?

**URL:** <https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779>\
**Category:** Using Django\
**Created:** [December 2, 2021, 5:30pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779 "2021-12-02T17:30:15Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![logikonabstractions](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/logikonabstractions/32/4066_2.png) [@logikonabstractions](https://forum.djangoproject.com/u/logikonabstractions)\
**Post date:** [December 2, 2021, 5:30pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/1 "2021-12-02T17:30:15Z")

</div>

Hey,  
Getting a bit of a corner case and wondering what you guys think.

So I’m importing fixtures, say this `test.json`:

```auto
[
    {
        "model": "admin_account.terme",
        "pk": 0,
        "fields":
        {
            "desc_fr": "Net 30 jours",
            "desc_en": "30 days net",
        }
    }
]

```

with model

```auto
class Terme(models.Model):
    desc_fr = models.CharField(max_length=25, verbose_name='Desc_fr', blank=True, null=True)
    desc_en = models.CharField(max_length=25, verbose_name='Desc_en', blank=True, null=True)

```

If I import it as-is, I get:

```auto
Traceback (most recent call last):
  File "/home/fv/Documents/pro-2020/voltec/VOLTEC-PROJECT/VOLTEC-WEBAPP/app/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.NumericValueOutOfRange: setval: value 0 is out of bounds for sequence "admin_account_terme_id_seq" (1..2147483647)

```

However, if I simply add another fixture, even in the same import, with id=0, then the importation works just fine.

I know from the doc that the default id field is AutoField, and at least SmallAutofield and BigAutoField mention values from 1…XXXXX.

The data I’m importing does contain a few id=0, and in the db they typically refer to unknown/missing/placeholder stuff. I am not 100% certain that I can just re-assign another id to that field, as perhaps other services expect this behavior.

So… am I likely fine to import id=0? Or should I take the issue to mean I need to find another way to do things?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 2, 2021, 6:28pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/2 "2021-12-02T18:28:49Z")

</div>

Conjecture / question - Is this only happening on the first import?

In other words, if you empty the table and do _not_ delete it, and then import the single value, does it work? (I have a hypothesis about this dealing with the status of a freshly-created sequence object.)

Also, if you import multiple entries all with the same PK=0, do you get multiple entries in the table, or just one?

Finally, don’t forget that you can use the `-v 3` parameter on loaddata to get more details logged.

---

<div class="post-metadata">

**Author:** ![logikonabstractions](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/logikonabstractions/32/4066_2.png) [@logikonabstractions](https://forum.djangoproject.com/u/logikonabstractions)\
**Post date:** [December 2, 2021, 9:48pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/3 "2021-12-02T21:48:45Z")

</div>

If I empty the table (without dropping it) & try to re-import, I still get the same error.

If I try to import id=0 alone, but in an already-populated table, it imports fine and shows up in the db.

If I import an id=0 AND an id=1, THEN I delete the id=1 entry, and try to import id=0 again, I still get the same error.

The additional logs didn’t turn up additional details.

To me it SEEMS like this is sort of a pg (or a pg db wrapper perhaps) issue, fairly confident I did the same thing with a sqlite3 backend earlier and didn’t have any issues

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 2, 2021, 10:29pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/4 "2021-12-02T22:29:30Z")

</div>

> [@logikonabstractions](#):
>
> `psycopg2.errors.NumericValueOutOfRange: setval: value 0 is out of bounds for sequence "admin_account_terme_id_seq" (1..2147483647)`

That error looks like it’s directly from PostgreSQL. I know that django auto fields create primary key sequences in the database. You can confirm in a psql shell with `\d <table_name>`. Looking at the [CREATE SEQUENCE docs](https://www.postgresql.org/docs/14/sql-createsequence.html) for postgres shows there is a minimum value option. I do not recall if Django will specify a custom minimum value. So assuming that it doesn’t, the docs indicate that the minimum value would be 1.

If you need to manipulate that sequence’s minimum value, you’re going to have to write a custom sql migration to modify that database instance.

_Personal opinion_  
If you can easily set that primary key to 1 rather than 0, I would go that route. I don’t like managing DB manually like that unless it’s absolutely necessary, really easy, or is very low risk.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 2, 2021, 10:38pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/5 "2021-12-02T22:38:26Z")

</div>

I’m finding it interesting that this also works for me _if_ there is already at least one row of data in that table. If the table is empty, I get that error. If I insert one row into the table, the loaddata completes without error - even if there’s only one record in the json file.

Also, if I have multiple records in the JSON file all with a pk of 0, only the last one is retained.

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 2, 2021, 10:52pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/6 "2021-12-02T22:52:04Z")

</div>

That is odd. I don’t have the ability to reproduce this quickly, but if you have an empty table and set the pk to 5, does it insert with a pk of 5? And what does that do to the sequence in the database? Is that set to 1 or 6?

Similarly, if there are elements in the table and you load an instance with a pk that’s greater than the next sequence value, what does that do to the sequence?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 2, 2021, 11:13pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/7 "2021-12-02T23:13:33Z")

</div>

> [@CodenameTim](#):
>
> Similarly, if there are elements in the table and you load an instance with a pk that’s greater than the next sequence value, what does that do to the sequence?

It sets the sequence to that last value inserted.

Subsequent “pk-less” records are inserted after that.

Now, if you go back and insert a lower pk, it does _not_ reset the sequence backward - and now I think I realize that’s likely to be the key to this issue.

With an empty table, inserting a row with pk=0 is going to try and set the `last_value` of the sequence to 0 - creating that error.

If there’s already data in the table, the `last_value` is already greater than 0 and an insert of pk=0 is _not_ going to try and reset it to an invalid value.

Also, if you’re trying to insert multiple rows, it appears that all the rows are being inserted as one transaction. The `last_value` is going to be set to the highest value, also preventing an error.

I think we’ve solved the mystery!

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 2, 2021, 11:18pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/8 "2021-12-02T23:18:59Z")

</div>

Thanks for doing that leg work!

---

<div class="post-metadata">

**Author:** ![logikonabstractions](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/logikonabstractions/32/4066_2.png) [@logikonabstractions](https://forum.djangoproject.com/u/logikonabstractions)\
**Post date:** [December 2, 2021, 11:19pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/9 "2021-12-02T23:19:14Z")

</div>

Right - it doesn’t care _per se_ about the pk/id value in the table… but it will not set the _last\_value_ to 0. So the auto-increment mechanism is the root cause.

Interesting - but then I’m going to think it’s probably fine having those id=0 there, just going to check that they fetch properly (they did with sqlite3) and I can edit the record.

So what… is that a minor bug? Should open a ticket on it or not?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 2, 2021, 11:28pm UTC](https://forum.djangoproject.com/t/issues-importing-fixtures-with-id-0-and-postgres-backend/10779/10 "2021-12-02T23:28:11Z")

</div>

It’s not a bug - definitely not in Django. If anything, it’s an application issue. I would no more expect Django to accept that than I would expect it to accept a negative value, a non-integer, or a non-numeric.

Quoting directly [from the docs](https://docs.djangoproject.com/en/3.2/ref/models/fields/#bigautofield):

> A 64-bit integer, much like an [`AutoField`](https://docs.djangoproject.com/en/3.2/ref/models/fields/#django.db.models.AutoField) except that it is guaranteed to fit numbers from `1` to `9223372036854775807` .

So zero is _not_ defined as a valid value. (Note also how it’s worded. It’s _not_ saying that those are the _only_ valid values, only that that is the range that is guaranteed to work. The results from using any other value is undefined.)

Also, what you need to verify is whether or not you’re going to get all those “id=0” rows inserted. It did _not_ work that way in my testing. Only the last row was in the table.