Django data migration using a PostgreSQL CTE#

1
with something as (
2
  select id, created_at from ...
3
)
4
update mytable
5
  set
6
    created_at = something.created_at
7
  from
8
    something
9
  where
10
    mytable.id = something.id

1
from django.db import migrations
2

3
SQL = """
4
with created_at_by_reversion as (
5
  select
6
    location.id as id, min(date_created) as created_at
7
  from location
8
    join reversion_version on (location.id = reversion_version.object_id::integer and reversion_version.content_type_id = 18)
9
    join reversion_revision on reversion_revision.id = reversion_version.revision_id
10
  group by location.id
11
),
12
created_at_by_source_location as (
13
  select
14
    location.id as id, min(source_location.created_at) as created_at
15
  from source_location
16
    join location on source_location.matched_location_id = location.id
17
  group by location.id
18
),
19
new_created_at_for_locations as (
20
  select
21
    location.id,
22
    created_at_by_reversion.created_at as created_at_by_reversion,
23
    created_at_by_source_location.created_at as created_at_by_source_location,
24
    coalesce(created_at_by_reversion.created_at, created_at_by_source_location.created_at) as new_created_at
25
  from location
26
    left join created_at_by_source_location on created_at_by_source_location.id = location.id
27
    left join created_at_by_reversion on created_at_by_reversion.id = location.id
28
)
29
update location
30
  set
31
    created_at = new_created_at_for_locations.new_created_at
32
  from
33
    new_created_at_for_locations
34
  where
35
    location.id = new_created_at_for_locations.id
36
"""
37

38

39
class Migration(migrations.Migration):
40

41
    dependencies = [
42
        ("core", "0132_location_created_at_created_by"),
43
    ]
44

45
    operations = [
46
        migrations.RunSQL(
47
            sql=SQL,
48
            reverse_sql=migrations.RunSQL.noop,
49
        ),
50
    ]