Hi Michel,
So, a little explanation of why you're facing this issue:
When you create an "auto-increment" field in Postgres, Postgres creates
something called a "sequence" to keep track of the increments. If you were
to `psql` into your database and check the schema of the table, you'll see
the `id` field defined with something like:
id | integer | | not null |
nextval('content_id_seq'::regclass)
What this means is that to assign the `id` to an object, it will call the
"nextval" function on the "content_id_seq" sequence, to get the next number
in the sequence. In the normal flow of things, this is not a problem. Every
time you insert a new record into the table, it increments the sequence by
calling the "nextval" operation and populates the id, and the same for the
subsequent row insertion and so on.
Now, the problem arises if you imported data "directly" into the database,
i.e. INSERTed rows including the id, in which case, the database will never
call the "nextval" function internally, and the "sequence" it uses to keep
track of the incrementing ids will never have been updated. I.e. if you
manually insert 3000 rows with ids, the nextval() operation on the sequence
is never called, and hence postgres still thinks the "sequence" is at 1,
and therefore tries to assign the id of, say "2" to the next item, and then
of course fails since there is already an item that you manually inserted
with id 2.
There would be two ways to solve this:
- 1> When importing the data, leave out the `id` field, and let postgres
populate the id field automatically using the auto-increment nextval()
function so the sequence stays in sync.
- 2> The other option is to manually reset the sequence to be at whatever
your highest id value is.
The way to manually update the sequence:
For each table where you are facing this problem:
Run `\d` to see the table schema and get the name of the "sequence" ^^ in
my example above, the sequence name is "content_id_seq" - it will generally
follow the format of <model_name>_id_seq .
Fetch the highest current id with:
SELECT id FROM <table_name> ORDER BY id DESC LIMIT 1;
Reset the sequence to the value of the highest id:
SELECT setval('content_id_seq', 4555, true); # assuming your sequence is
called content_id_seq and the highest id was 4555 .
If doing this individually for each table in the db seems a bit tedious,
you will find various scripts and solutions to automate the process a bit
more by searching for something like "postgres reset sequence" - this is a
fairly common problem and you should find a bunch of material online.
I hope that helps!
-Sanjay
On Fri, Oct 26, 2018 at 6:50 AM Michel Lavoie <lavoie.mic...@gmail.com>
wrote:
> Hi,
>
> I migrated my previous sqlite3 installation to postgresql, following this
> discussion:
> https://groups.google.com/forum/#!msg/django-users/VGG8GTAX4l0/tQUW20HcAwAJ
>
> I'm not facing another weird issue: Whenever I try to create a new item
> (finance.models.Debtor, see
> https://github.com/miek770/huitcent/blob/master/finance/models.py),
> django fails and tells me that primary key (1) is taken. Indeed, the next
> free id is around 3100. The time after that it tries to take id (2), then
> (3), and so on. The error looks like this:
>
> IntegrityError at /finance/2/add_transaction/
>
> duplicate key value violates unique constraint "finance_debtor_pkey"
> DETAIL: Key (id)=(3) already exists.
>
> Request Method: POST
> Request URL: https://myserver/finance/2/add_transaction/
> Django Version: 2.1.2
> Exception Type: IntegrityError
> Exception Value:
>
> duplicate key value violates unique constraint "finance_debtor_pkey"
> DETAIL: Key (id)=(3) already exists.
>
> Exception Location: /srv/http/huitcent/lib/python3.5/site-packages/django/
> db/backends/utils.py in _execute, line 85
> Python Executable: /srv/http/huitcent/bin/uwsgi
> Python Version: 3.5.3
> Python Path:
>
> ['.',
> '',
> '/srv/http/huitcent/lib/python35.zip',
> '/srv/http/huitcent/lib/python3.5',
> '/srv/http/huitcent/lib/python3.5/plat-arm-linux-gnueabihf',
> '/srv/http/huitcent/lib/python3.5/lib-dynload',
> '/usr/lib/python3.5',
> '/usr/lib/python3.5/plat-arm-linux-gnueabihf',
> '/srv/http/huitcent/lib/python3.5/site-packages']
>
> Server time: jeu, 25 Oct 2018 21:00:14 -0400
>
> As I mentioned before, there are indeed existing entries with these ids.
> My table definition in postgresql (version 10 looks like this:
>
> forum=# \d+ finance_debtor
> Table
> "public.finance_debtor"
> Column | Type | Collation | Nullable |
> Default | Storage | Stats target | Description
>
> ----------------+---------+-----------+----------+--------------------------------------------+---------+--------------+-------------
> id | integer | | not null |
> nextval('finance_debtor_id_seq'::regclass) | plain | |
> transaction_id | integer | | not null |
> | plain | |
> user_id | integer | | not null |
> | plain | |
> Indexes:
> "finance_debtor_pkey" PRIMARY KEY, btree (id)
> "finance_debtor_transaction_id_7098fd56" btree (transaction_id)
> "finance_debtor_user_id_90f84080" btree (user_id)
> Foreign-key constraints:
> "finance_debtor_transaction_id_7098fd56_fk_finance_t" FOREIGN KEY
> (transaction_id) REFERENCES finance_transaction(id) DEFERRABLE INITIALLY
> DEFERRED
> "finance_debtor_user_id_90f84080_fk_auth_user_id" FOREIGN KEY
> (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
>
> And here's postgresql's log output:
>
> 2018-10-25 21:00:14.052 EDT [13645] forum@forum ERROR: duplicate key
> value violates unique constraint "finance_debtor_pkey"
> 2018-10-25 21:00:14.052 EDT [13645] forum@forum DETAIL: Key (id)=(3)
> already exists.
> 2018-10-25 21:00:14.052 EDT [13645] forum@forum STATEMENT: INSERT INTO
> "finance_debtor" ("user_id", "transaction_id") VALUES (10, 3) RETURNING
> "finance_debtor"."id"
>
> It looks weird... the SQL statement looks just fine, it's not specifying
> the id but the other fields... It (the table) was created automatically by
> django, and I transferred the data using CSV files, making sure the column
> order matched the new database.
>
> I also tried addind the following line to my Debtor model, with no impact:
>
> id = models.AutoField(primary_key=True)
>
>
> Any idea what could be wrong here? Does this look like an internal issue
> for postgresql, or an error during the table's initiation by django, or
> something else?
>
> Thanks,
>
> Michel
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users+unsubscr...@googlegroups.com.
> To post to this group, send email to django-users@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/ab6877fd-235a-4916-bfca-f1f2c0f0f3e1%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/ab6877fd-235a-4916-bfca-f1f2c0f0f3e1%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAG3W7ZH3DATNa0UAFZ8iLCwxa4PUz6FuXxjUjA%3D_n6s4Z44EdA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
