2

I had this problem when I try to save a new entry into a table called 'config',

class Config(models.Model):
    ident = models.CharField(max_length=uuidLength, null=True, editable=False)
    scanner = models.ForeignKey('Scanner')
    name = models.CharField(max_length=64)
   ''' some other fields '''

and postgres gave such error(the app is called "pegasus" so the table name that django gives is actually "pegasus_config"):

IntegrityError: duplicate key value violates unique constraint "pegasus_config_scanner_id_name_key"
DETAIL:  Key (scanner_id, name)=(2, ) already exists.

I searched in stackoverflow and found this solution, the problem is that I don't know which table should I reset the index. I did the following according to the answer:

SELECT setval('pegasus_config_id_seq', (SELECT MAX(id) FROM pegasus_config)+1)

but the problem still exists. I also went into database and found that "pegasus_config_scanner_id_name_key" is actually an index. So I'm confused about which index to reset? Please help. Thanks.

Community
  • 1
  • 1
Shang Wang
  • 24,909
  • 20
  • 73
  • 94
  • I think you should drop that index. Seems to me that table is taking as Key the `scanner_id` and the `name` field, both. Is this what you want? – Paulo Bu Jun 26 '13 at 14:23

1 Answers1

1

You can try a query like the following to determine which table has that unique constraint defined:

SELECT  n.nspname as schema_name,
        co.conrelid::regclass as table_name,
        co.conname as constraint_name,
        pg_catalog.pg_get_constraintdef(co.oid, true) as constraing_def
FROM pg_constraint co
INNER JOIN pg_catalog.pg_class cl ON cl.oid = co.conrelid
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = cl.relnamespace
WHERE co.conname = 'pegasus_config_scanner_id_name_key'
AND co.contype = 'u'
bma
  • 9,424
  • 2
  • 33
  • 22
  • Note also that UNIQUE constraints/indexes defined over columns that are defined as NOT NULL can have duplicate entries if one of the values in the constrained columns is NULL. I'm not suggesting that's the problem here, but it came to mind when I saw the NULL in the constraint violation (the "name" column). – bma Jun 26 '13 at 16:42