til: django_migration-using-cte.md
This data as json
| path | topic | title | url | body | html | shot | created | created_utc | updated | updated_utc | shot_hash | slug |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| django_migration-using-cte.md | django | Django data migration using a PostgreSQL CTE | https://github.com/simonw/til/blob/main/django/migration-using-cte.md | I figured out how to use a PostgreSQL CTE as part of an update statement in a Django data migration. The trick here is mainly understanding how to combine CTEs with a PostgreSQL update - here's the pattern for that: ```sql with something as ( select id, created_at from ... ) update mytable set created_at = something.created_at from something where mytable.id = something.id ``` Here's the full migration I wrote: ```python from django.db import migrations SQL = """ with created_at_by_reversion as ( select location.id as id, min(date_created) as created_at from location join reversion_version on (location.id = reversion_version.object_id::integer and reversion_version.content_type_id = 18) join reversion_revision on reversion_revision.id = reversion_version.revision_id group by location.id ), created_at_by_source_location as ( select location.id as id, min(source_location.created_at) as created_at from source_location join location on source_location.matched_location_id = location.id group by location.id ), new_created_at_for_locations as ( select location.id, created_at_by_reversion.created_at as created_at_by_reversion, created_at_by_source_location.created_at as created_at_by_source_location, coalesce(created_at_by_reversion.created_at, created_at_by_source_location.created_at) as new_created_at from location left join created_at_by_source_location on created_at_by_source_location.id = location.id left join created_at_by_reversion on created_at_by_reversion.id = location.id ) update location set created_at = new_created_at_for_locations.new_created_at from new_created_at_for_locations where location.id = new_created_at_for_locations.id """ class Migration(migrations.Migration): dependencies = [ ("core", "0132_location_created_at_created_by"), ] operations = [ migrations.RunSQL( sql=SQL, reverse_sql=migrations.RunSQL.noop, ), ] ``` | <p>I figured out how to use a PostgreSQL CTE as part of an update statement in a Django data migration. The trick here is mainly understanding how to combine CTEs with a PostgreSQL update - here's the pattern for that:</p> <div class="highlight highlight-source-sql"><pre>with something <span class="pl-k">as</span> ( <span class="pl-k">select</span> id, created_at <span class="pl-k">from</span> ... ) <span class="pl-k">update</span> mytable <span class="pl-k">set</span> created_at <span class="pl-k">=</span> <span class="pl-c1">something</span>.<span class="pl-c1">created_at</span> <span class="pl-k">from</span> something <span class="pl-k">where</span> <span class="pl-c1">mytable</span>.<span class="pl-c1">id</span> <span class="pl-k">=</span> <span class="pl-c1">something</span>.<span class="pl-c1">id</span></pre></div> <p>Here's the full migration I wrote:</p> <div class="highlight highlight-source-python"><pre><span class="pl-k">from</span> <span class="pl-s1">django</span>.<span class="pl-s1">db</span> <span class="pl-k">import</span> <span class="pl-s1">migrations</span> <span class="pl-v">SQL</span> <span class="pl-c1">=</span> <span class="pl-s">"""</span> <span class="pl-s">with created_at_by_reversion as (</span> <span class="pl-s"> select</span> <span class="pl-s"> location.id as id, min(date_created) as created_at</span> <span class="pl-s"> from location</span> <span class="pl-s"> join reversion_version on (location.id = reversion_version.object_id::integer and reversion_version.content_type_id = 18)</span> <span class="pl-s"> join reversion_revision on reversion_revision.id = reversion_version.revision_id</span> <span class="pl-s"> group by location.id</span> <span class="pl-s">),</span> <span class="pl-s">created_at_by_source_location as (</span> <span class="pl-s"> select</span> <span class="pl-s"> location.id as id, min(source_location.created_at) as created_at</span> <span class="pl-s"> from source_location</span> <span class="pl-s"> join location on source_location.matched_location_id = location.id</span> <span class="pl-s"> group by location.id</span> <span class="pl-s">),</span> <span class="pl-s">new_created_at_for_locations as (</span> <span class="pl-s"> select</span> <span class="pl-s"> location.id,</span> <span class="pl-s"> created_at_by_reversion.created_at as created_at_by_reversion,</span> <span class="pl-s"> created_at_by_source_location.created_at as created_at_by_source_location,</span> <span class="pl-s"> coalesce(created_at_by_reversion.created_at, created_at_by_source_location.created_at) as new_created_at</span> <span class="pl-s"> from location</span> <span class="pl-s"> left join created_at_by_source_location on created_at_by_source_location.id = location.id</span> <span class="pl-s"> left join created_at_by_reversion on created_at_by_reversion.id = location.id</span> <span class="pl-s">)</span> <span class="pl-s">update location</span> <span class="pl-s"> set</span> <span class="pl-s"> created_at = new_created_at_for_locations.new_created_at</span> <span class="pl-s"> from</span> <span class="pl-s"> new_created_at_for_locations</span> <span class="pl-s"> where</span> <span class="pl-s"> location.id = new_created_at_for_locations.id</span> <span class="pl-s">"""</span> <span class="pl-k">class</span> <span class="pl-v">Migration</span>(<span class="pl-s1">migrations</span>.<span class="pl-v">Migration</span>): <span class="pl-s1">dependencies</span> <span class="pl-c1">=</span> [ (<span class="pl-s">"core"</span>, <span class="pl-s">"0132_location_created_at_created_by"</span>), ] <span class="pl-s1">operations</span> <span class="pl-c1">=</span> [ <span class="pl-s1">migrations</span>.<span class="pl-v">RunSQL</span>( <span class="pl-s1">sql</span><span class="pl-c1">=</span><span class="pl-v">SQL</span>, <span class="pl-s1">reverse_sql</span><span class="pl-c1">=</span><span class="pl-s1">migrations</span>.<span class="pl-v">RunSQL</span>.<span class="pl-s1">noop</span>, ), ]</pre></div> | <Binary: 48,166 bytes> | 2021-05-17T17:04:29-07:00 | 2021-05-18T00:04:29+00:00 | 2021-05-17T17:04:29-07:00 | 2021-05-18T00:04:29+00:00 | 4f4a982442ef5d9b9bb40127c0d7949e | migration-using-cte |