Menu
I recently imported a lot of data from an old database into a new Postgresql database as the basis for models in a new Django site.
I used the IDs from the old database (as rows in various tables refer to each other), but they aren't all sequential - there are often large gaps.
I've noticed that when I add a new object via the Django app, then it has been using IDs starting from 1, which hasn't been a problem so far as there were no rows with very low IDs.
But once it reaches the first row of legacy data, then postgres obviously complains:
ERROR: duplicate key value violates unique constraint "django_comments_pkey" DETAIL: Key (id)=(25) already exists. Looking at the table descriptions I'm guessing I need to reset some kind of sequence on each table:
Table "public.django_comments" Column | Type | Modifiers -----------------+--------------------------+-------------------------------------------------------------- id | integer | not null default nextval('django_comments_id_seq'::regclass) ... What do I need to do to reset that sequence, so that new rows are added with IDs higher than the current maximum ID?
534
pg_get_serial_sequence can be used to avoid any incorrect assumptions about the sequence name. This resets the sequence in one shot: SELECT pg_catalog. setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);
ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings. You must own the sequence to use ALTER SEQUENCE . To change a sequence's schema, you must also have CREATE privilege on the new schema.
Run sqlsequencereset and it'll print all the reset commands you need.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
