6

I Have a project built in django and it uses a postgres database. This database was populated by CSVs files. So when I want to insert a new object I got the error "duplicated key" because the object with id = 1 already exists.

The code :

user = User(name= "Foo")
user.save()

The table users has the PK on the id. 

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

If I get the table's details in psql I got:

Column| Type    |  Modifiers                          
------+-------- +--------------------------------------
id    | integer | not null default nextval('users_id_seq'::regclass)

Additionally, if I do user.dict after create the variable user and before saving it, I get 'id': None

How can I save the user with an id that is not being used?

nacho c
  • 311
  • 1
  • 3
  • 15

4 Answers4

11

You most likely inserted your Users from the CSV setting the id value explicitly, when this happens the postgres sequence is not updated and as a result of that when you try to add a new user the sequence generates an already used value

Check this other question for reference postgres autoincrement not updated on explicit id inserts

The solution is what the answer for that question says, update your sequence manually

Community
  • 1
  • 1
armonge
  • 3,108
  • 20
  • 36
8

You can fix it by setting users_id_seq manually.

SELECT setval('users_id_seq', (SELECT MAX(id) from "users"));
akarca
  • 1,456
  • 15
  • 12
0

Unless you have name as a primary key for the table the above insert should work. If you have name as primary key remove it and try it.

Raja
  • 5,793
  • 1
  • 17
  • 20
0

In Postgres SQL you can specify id as serial and you can mark it as Primary Key.Then whenever you will insert record , it will be in a sequence. i.e id serial NOT NULL and CONSTRAINT primkey PRIMARY KEY (id).

As you said its a pre populated by CSV , so when you insert it from python code it will automatically go the end of the table and there will be no duplicate values.

Ankur Srivastava
  • 855
  • 9
  • 10
  • The id is set as serial take a look at the description, I updated the question and put what psql says about the table – nacho c Jan 29 '17 at 00:28
  • 1
    Use cursor.lastrowid to get the last row ID inserted on the cursor object, or connection.insert_id() to get the ID from the last insert on that connection. – Ankur Srivastava Jan 29 '17 at 00:36