Getting DatabaseError: multiple default values specified for column

Question

I can't figure out what to do to avoid this error whenever I try to make a migration using South in one of my Django projects:

ERROR:

Running migrations for askbot:

Migrating forwards to 0006_auto__del_field_tagplus_tag_ptr__add_field_tagplus_id__add_field_tagpl.

askbot:0006_auto__del_field_tagplus_tag_ptr__add_field_tagplus_id__add_field_tagpl

FATAL ERROR - The following SQL query failed: ALTER TABLE "tagplus" ADD COLUMN "id" serial NOT >NULL PRIMARY KEY DEFAULT -1; The error was: multiple default values specified for column "id" of table "tagplus"

Error in migration: >askbot:0006_auto__del_field_tagplus_tag_ptr__add_field_tagplus_id__add_field_tagpl DatabaseError: multiple default values specified for column "id" of table "tagplus"

MIGRATION FILE 0006 CODE (Partial):

class Migration(SchemaMigration):

def forwards(self, orm):
    # Deleting field 'TagPlus.tag_ptr'
    db.delete_column(u'tagplus', u'tag_ptr_id')

    # Adding field 'TagPlus.id'
    db.add_column(u'tagplus', u'id',
                  self.gf('django.db.models.fields.AutoField')(default=0, primary_key=True),
                  keep_default=False)

    # Adding field 'TagPlus.name'
    db.add_column(u'tagplus', 'name',
                  self.gf('django.db.models.fields.CharField')(default=0, unique=True, max_length=255),
                  keep_default=False)

Thanks!

EDIT:

I Guess the error has something to do with this choice I was prompted while creating the migration file.

 ? The field 'TagPlus.tag_ptr' does not have a default specified, yet is NOT NULL.
 ? Since you are removing this field, you MUST specify a default
 ? value to use for existing rows. Would you like to:
 ?  1. Quit now.
 ?  2. Specify a one-off value to use for existing columns now
 ?  3. Disable the backwards migration by raising an exception; you can edit the migration to fix it later
 ? Please select a choice:

I selected 'specify one-off value' and I set this value to 0

Rahul · Accepted Answer · 2014-05-07 21:04:51Z

5

You are anyways saying keep_default=False. So remove that default=0 from your code

   db.add_column(u'tagplus', u'id',
                  self.gf('django.db.models.fields.AutoField')(primary_key=True),
                  keep_default=False)

Per SQL it should be (remove the NOT NULL)

ALTER TABLE tagplus ADD COLUMN id serial PRIMARY KEY

See this document which explains the reason behind this error http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL

edited May 7, 2014 at 21:04

answered May 7, 2014 at 20:37

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andreu C. Over a year ago

No luck with this, I'm getting: Error in migration: askbot:0007_auto__del_field_tagplus_tag_ptr__add_field_tagplus_id__add_field_tagpl DatabaseError: type "serial" does not exist

Andreu C. Over a year ago

Sorry, I'm new in SQL, is there any way I can remove the NOT NULL using the python code and not raw SQL?.

Rahul Over a year ago

@AndreuC., nice question!!! have no idea about Python. Moreover, I have answered this question solely cause it's is tagged with postgreSQL. Sorry.. If the answer isn't much help to you ... then I should delete it. let me know.

Rahul Over a year ago

@AndreuC., in that case try accepting it as answer.

skyahead · Accepted Answer · 2014-05-25 16:05:31Z

There are two things to note:

Django will use 'id' as the default primary key if you haven't manually set one before, see here.

Postgres does not really have a 'serial' type. To resolve this issue, try to replace:

# Adding field 'TagPlus.id'
db.add_column(u'tagplus', u'id', self.gf('django.db.models.fields.AutoField')(default=0, primary_key=True), keep_default=False)

with:

    # Adding field 'TagPlus.id'
    if 'postgres' in db.backend_name.lower():
        db.execute("CREATE SEQUENCE tagplus_id_seq")
        db.execute("SELECT setval('tagplus_id_seq', (SELECT MAX(id) FROM tagplus))")
        db.execute("ALTER TABLE tagplus ADD COLUMN id SET DEFAULT nextval('tagplus_id_seq'::regclass)")
        db.execute("ALTER SEQUENCE tagplus_id_seq OWNED BY tagplus.id")
    else:
        db.add_column(u'tagplus', u'id', self.gf('django.db.models.fields.AutoField')(default=0, primary_key=True), keep_default=False)

Collectives™ on Stack Overflow

Getting DatabaseError: multiple default values specified for column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related