Django-Cities-Light, Changing fields to ForeignKey in postgresql raises error
Question: My goal is to use the Django-Cities-Light and link my models with foreignkey
to City and Country models from django-cities-light.

When I run python3 manage.py migrate I get the following error:

    
    
    Operations to perform:
      Synchronize unmigrated apps: gis, crispy_forms, geoposition, messages, staticfiles
      Apply all migrations: amenities, sites, images, venues, sessions, contenttypes, admin, auth, newsletter, registration, easy_thumbnails, cities_light
    Synchronizing apps without migrations:
      Creating tables...
        Running deferred SQL...
      Installing custom SQL...
    Running migrations:
      Rendering model states... DONE
      Applying venues.0012_auto_20160514_2024...Traceback (most recent call last):
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
        return self.cursor.execute(sql, params)
    psycopg2.ProgrammingError: column "venue_city_id" cannot be cast automatically to type integer
    HINT:  Specify a USING expression to perform the conversion.
    
    
    The above exception was the direct cause of the following exception:
    
    Traceback (most recent call last):
      File "manage.py", line 10, in <module>
        execute_from_command_line(sys.argv)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 354, in execute_from_command_line
        utility.execute()
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 346, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 394, in run_from_argv
        self.execute(*args, **cmd_options)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 445, in execute
        output = self.handle(*args, **options)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 222, in handle
        executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/executor.py", line 110, in migrate
        self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/executor.py", line 148, in apply_migration
        state = migration.apply(state, schema_editor)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/migration.py", line 115, in apply
        operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/operations/fields.py", line 201, in database_forwards
        schema_editor.alter_field(from_model, from_field, to_field)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 484, in alter_field
        old_db_params, new_db_params, strict)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 636, in _alter_field
        params,
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 111, in execute
        cursor.execute(sql, params)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute
        return super(CursorDebugWrapper, self).execute(sql, params)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
        return self.cursor.execute(sql, params)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/utils.py", line 98, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
        raise value.with_traceback(tb)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
        return self.cursor.execute(sql, params)
    django.db.utils.ProgrammingError: column "venue_city_id" cannot be cast automatically to type integer
    HINT:  Specify a USING expression to perform the conversion.
    

when i run python3 manage.py sqlmigrate venues 0012 i get the following
traceback:

    
    
    Traceback (most recent call last):
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/apps/registry.py", line 148, in get_app_config
        return self.app_configs[app_label]
    KeyError: 'cities_light'
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/state.py", line 238, in __init__
        model = self.get_model(lookup_model[0], lookup_model[1])
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/apps/registry.py", line 202, in get_model
        return self.get_app_config(app_label).get_model(model_name.lower())
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/apps/registry.py", line 150, in get_app_config
        raise LookupError("No installed app with label '%s'." % app_label)
    LookupError: No installed app with label 'cities_light'.
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "manage.py", line 10, in <module>
        execute_from_command_line(sys.argv)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 354, in execute_from_command_line
        utility.execute()
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 346, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 394, in run_from_argv
        self.execute(*args, **cmd_options)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/commands/sqlmigrate.py", line 31, in execute
        return super(Command, self).execute(*args, **options)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 445, in execute
        output = self.handle(*args, **options)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/commands/sqlmigrate.py", line 57, in handle
        sql_statements = executor.collect_sql(plan)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/executor.py", line 127, in collect_sql
        state = migration.apply(state, schema_editor, collect_sql=True)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/migration.py", line 115, in apply
        operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/operations/fields.py", line 186, in database_forwards
        to_model = to_state.apps.get_model(app_label, self.model_name)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/utils/functional.py", line 59, in __get__
        res = instance.__dict__[self.name] = self.func(instance)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/state.py", line 166, in apps
        return StateApps(self.real_apps, self.models)
      File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/state.py", line 248, in __init__
        raise ValueError(msg.format(field=operations[0][1], model=lookup_model))
    ValueError: Lookup failed for model referenced by field venues.Venue.venue_city: cities_light.City
    

This is the 0012 migration file:

    
    
    # -*- coding: utf-8 -*-
    from __future__ import unicode_literals
    
    from django.db import migrations, models
    
    
    class Migration(migrations.Migration):
    
        dependencies = [
            ('venues', '0011_venue_map_activation'),
        ]
    
        operations = [
            migrations.AlterField(
                model_name='venue',
                name='venue_city',
                field=models.ForeignKey(to='cities_light.City', blank=True),
            ),
        ]
    

My models are as follows:

    
    
    class Venue(models.Model):
        venue_owner = models.ForeignKey(User, null=True)
        venue_name = models.CharField(max_length=100)
        venue_address = models.CharField(max_length=250)
        venue_city = models.CharField(max_length=250)
        venue_zipcode = models.CharField(max_length=20, blank=True)
        venue_seated_capacity = models.PositiveIntegerField()
        venue_standing_capacity = models.PositiveIntegerField()
        venue_type = models.CharField(max_length=20, choices=VENUE_TYPES)
        venue_sqf = models.PositiveIntegerField(default=0)
        venue_description = models.TextField(blank=False, null=True)
        featurete = models.PositiveIntegerField(default=0)
        carousel = models.PositiveIntegerField(default=0)
        gallery = models.PositiveIntegerField(default=0)
        #map_activation is for activating the map for the venue
        map_activation = models.BooleanField(default=False)
        position = GeopositionField(blank=True)
    

My settings are as follows:

    
    
    INSTALLED_APPS = (
        'django.contrib.admin',
        'django.contrib.auth',
        'django.contrib.sites',
        'django.contrib.contenttypes',
        'django.contrib.sessions',
        'django.contrib.messages',
        'django.contrib.staticfiles',
        #GEOdjango
        'django.contrib.gis',
        #Django cities Light
        'cities_light',
        #Custom Apps
        'venues',
        'images',
        'amenities',
        #Third party apps
        'registration',
        'crispy_forms',
        'easy_thumbnails',
        'geoposition',
    )
    
    
    CITIES_LIGHT_TRANSLATION_LANGUAGES = ['nl', 'en']
    CITIES_LIGHT_INCLUDE_COUNTRIES = ['NL']
    

This caused the error. Changing venue_city from charfield to foreignkey. I
tried changing the models back but I still get the same error.

How can I fix this error and create the connections with the Django-cities-
models?


Answer: Assuming that your venue_city column at the moment contains integer values
that correspond to primary keys in the cities model. You can still carry out
the migration by editing the 0012_auto_20160514_2024 migration file. First do

    
    
    ./manage.py sqlmigrate venues 0012 
    

Watch closely for the bit of generated SQl that corresponds to the venue_city
column. Now you need to edit the migration file and replace the default
generated migration with a migrations.RunSQL. The query that goes into it is
essentially the query that you found with sqlmigration. Just add `USING
venue_city::integer` to the end.

**Update** : Based on your updated answer, you would replace this section

    
    
        migrations.AlterField(
            model_name='venue',
            name='venue_city',
            field=models.ForeignKey(to='cities_light.City', blank=True),
        ),
    

with custom SQL that modifies the column.

**Update 2** : Since you are unable to run `sqlmigrate` I will try to show you
what that SQL should be.

    
    
    migrations.RunSQL(''' ALTER TABLE venues_venue ALTER venue_city TYPE integer USING  venue_city::integer '''),
    
    migrations.RunSQL(''' ALTER TABLE venues_venue ALTER venue_city DROP NOT NULL '''),
    
    migrations.RunSQL(''' ALTER TABLE venues_venue ALTER venue_city RENAME COLUMN venue_city TO venue_city_id '''),
    
    migrations.RunSQL(''' ALTER TABLE venues_venue ADD CONSTRAINT venues_venus_somefk FOREIGN KEY (venue_city_id) REFERENCES  cities_light (id) DEFERRABLE INITIALLY DEFERRED'''),
    

With the full query essentially being what sqlmigrate shows it to be.

