Opened 9 years ago
Closed 6 years ago
#25678 closed Bug (fixed)
migrate scalar to array field in PostrgreSQL fails
Reported by: | Mark Mikofski | Owned by: | nobody |
---|---|---|---|
Component: | Migrations | Version: | dev |
Severity: | Normal | Keywords: | PostgreSQL, ArrayField |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Django-1.8.5
Python 2.7.10
PostgreSQL-9.4
Problem: Scalar type might not be cast to array of same type explicitly with USING
SQL command?
Solution: Use psql to migrate fields manually
dev=# ALTER TABLE my_app_mymodel ALTER COLUMN "my_field" TYPE double precision [] USING array["my_field"]::double precision[]; ALTER TABLE
Details
given an initial model:
class MyModel(models.Model): my_field = models.FloatField()
change this to ArrayField
class MyModel(models.Model): my_field = ArrayField( base_field=models.FloatField(), default=list )
then make migrations and migrate
$ ./manage.py makemigrations $ ./manage.py migrate
returns the following traceback:
Operations to perform: Synchronize unmigrated apps: staticfiles, messages Apply all migrations: admin, contenttypes, my_app, auth, sessions Synchronizing apps without migrations: Creating tables... Running deferred SQL... Installing custom SQL... Running migrations: Rendering model states... DONE Applying my_app.0XYZ_auto_YYYYMMDD_hhmm...Traceback (most recent call last): File "./manage.py", line 10, in <module> execute_from_command_line(sys.argv) File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line 351, in execute_from_command_line utility.execute() File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line 343, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "C:\Python27\lib\site-packages\django\core\management\base.py", line 394, in run_from_argv self.execute(*args, **cmd_options) File "C:\Python27\lib\site-packages\django\core\management\base.py", line 445, in execute output = self.handle(*args, **options) File "C:\Python27\lib\site-packages\django\core\management\commands\migrate.py", line 222, in handle executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial) File "C:\Python27\lib\site-packages\django\db\migrations\executor.py", line 110, in migrate self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial) File "C:\Python27\lib\site-packages\django\db\migrations\executor.py", line 148, in apply_migration state = migration.apply(state, schema_editor) File "C:\Python27\lib\site-packages\django\db\migrations\migration.py", line 115, in apply operation.database_forwards(self.app_label, schema_editor, old_state, project_state) File "C:\Python27\lib\site-packages\django\db\migrations\operations\fields.py", line 201, in database_forwards schema_editor.alter_field(from_model, from_field, to_field) File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py", line 484, in alter_field old_db_params, new_db_params, strict) File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py", line 636, in _alter_field params, File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py", line 111, in execute cursor.execute(sql, params) File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line 79, in execute return super(CursorDebugWrapper, self).execute(sql, params) File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params) File "C:\Python27\lib\site-packages\django\db\utils.py", line 97, in __exit__ six.reraise(dj_exc_type, dj_exc_value, traceback) File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: column "my_field " cannot be cast automatically to type double precision[] HINT: Specify a USING expression to perform the conversion.
Change History (8)
comment:1 by , 9 years ago
comment:2 by , 9 years ago
Version: | 1.8 → master |
---|
The USING
part was added by #25002 which should be part of Django 1.9 but scalar to array conversion might require adjustment.
Can you confirm the issue also exists in 1.9?
comment:3 by , 9 years ago
I'm working on a patch for this:
- add new SQL statement here: django/db/backends/base/schema.py:45
sql_alter_column_array = "ALTER COLUMN %(column)s TYPE %(type)s USING array[%(column)s]::%(type)s"
- in
_alter_field()
at L555 check if casting to an array and call something similar to_alter_column_type_sql()
at L753 with new SQL statements then return fragments.
comment:4 by , 9 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Type: | Uncategorized → Bug |
comment:5 by , 9 years ago
@mikofski I suggest we name the attribute sql_alter_scalar_column_to_array
instead and add a test to make sure array
to array
conversion doesn't use it (e.g. text[]
-> int[]
)
comment:6 by , 9 years ago
@charettes okay, just catching up - I haven't checked django-1.9 yet, I should do that first to see if any changes are needed. Thanks!
comment:7 by , 8 years ago
Keywords: | migrate removed |
---|
comment:8 by , 6 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
this was fixed in #25002 in postgresql.schema.py:8 by commit 73040e58 thanks!
using
sqlmigrate
it can be seen that the SQL used does not include theUSING
command to cast the old column to the new typeit should be ...