How to change text fields to a real UUID type for Django and PostgreSQL

28 August 2015

Switch to a dedicated UUID field type, they said, it'll be better they said.

Things are rarely simple.

If you've been storing UUID's as text in Django and PostgreSQL and now want to take advantage of the dedicated UUID type of both Django >= 1.8 and Postgres >= 8.3 then you're wanting to migrate your fields. But the process is not as simple as just changing the type of the field in the model like it usually is.

Postgres does have a way to convert text to uuid, but you have to do it manually because Django won't put that into the migration for you.

Django will most likely create a migration for you that looks something like:

class Migration(migrations.Migration):

    dependencies = [
        ('app', '0001_auto'),
    ]

    operations = [
        migrations.AlterField(
            model_name='modelname',
            name='uuid',
            field=models.UUIDField(db_index=True, unique=True),
        ),
    ]

If your uuid field has no indexes or constraints, then you might actually be done, in which case I doubt you're reading this. For those who've found this, it's likely because you have one or both of indexes and constraints.

Here is what I had to do to make this work.

First, put the auto created migration operations into a RunSQL operation as the state_operations parameter. This allows you to provide a custom migration, but keep Django informed about what's happened to the database schema.

class Migration(migrations.Migration):

    dependencies = [
        ('app', '0001_auto'),
    ]

    operations = [
    migrations.RunSQL(sql_commands, None, [
            migrations.AlterField(
                model_name='modelname',
                name='uuid',
                field=models.UUIDField(db_index=True, unique=True),
            ),
        ]),
    ]

Now you'll need to provide some SQL commands for that sql_commands variable. I opted to put the sql into a separate file and then load in with the following python code:

sql_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '0001.sql')
with open(sql_path, "r") as sqlfile:
    sql_commands = sqlfile.read()

Now for the real tricky part, where we actually perform the migration. The basic command you want looks like:

alter table tablename alter column uuid type uuid using uuid::uuid;

But the reason we are here is because of indexes. And as I discovered, Django likes to use your migrations to created randomly named indexes on your fields while running tests, so your tests will fail if you just delete and then recreate a fixed name index or two. So the following is sql that will delete one constraint and all indexes on the text field before converting to a uuid field. It also works for multiple tables in one go.

DO $$
DECLARE
    table_names text[];
    this_table_name text;
    the_constraint_name text;
    index_names record;

BEGIN

SELECT array['table1',
             'table2'
             ]
    INTO table_names;


FOREACH this_table_name IN array table_names
LOOP
    RAISE notice 'migrating table %', this_table_name;

    SELECT CONSTRAINT_NAME INTO the_constraint_name
    FROM information_schema.constraint_column_usage
    WHERE CONSTRAINT_SCHEMA = current_schema()
        AND COLUMN_NAME IN ('uuid')
        AND TABLE_NAME = this_table_name
    GROUP BY CONSTRAINT_NAME
    HAVING count(*) = 1;
    if the_constraint_name is not NULL then
        RAISE notice 'alter table % drop constraint %',
            this_table_name,
            the_constraint_name;
        execute 'alter table ' || this_table_name
            || ' drop constraint ' || the_constraint_name;
    end if;

    FOR index_names IN
    (SELECT i.relname AS index_name
     FROM pg_class t,
          pg_class i,
          pg_index ix,
          pg_attribute a
     WHERE t.oid = ix.indrelid
         AND i.oid = ix.indexrelid
         AND a.attrelid = t.oid
         AND a.attnum = any(ix.indkey)
         AND t.relkind = 'r'
         AND a.attname = 'uuid'
         AND t.relname = this_table_name
     ORDER BY t.relname,
              i.relname)
    LOOP
        RAISE notice 'drop index %', quote_ident(index_names.index_name);
        EXECUTE 'drop index ' || quote_ident(index_names.index_name);
    END LOOP; -- index_names

    RAISE notice 'alter table % alter column uuid type uuid using uuid::uuid;',
        this_table_name;
    execute 'alter table ' || quote_ident(this_table_name)
        || ' alter column uuid type uuid using uuid::uuid;';
    RAISE notice 'CREATE UNIQUE INDEX %_uuid ON % (uuid);',
        this_table_name, this_table_name;
    execute 'create unique index ' || this_table_name || '_uuid on '
        || this_table_name || '(uuid);';

END LOOP; -- table_names

END;
$$

Hopefully this helps you move from text fields to uuid fields without having to do all the work I had to.

I made a cell phone 

18 August 2015

What do you do when your 2G phone finally won't work anymore? You create your own phone of course!

How to adjust car mirrors correctly 

12 June 2015

I am astounded that I've never come across this before. It really does eliminate the blind spot.

Passing cars on the M7 today, I watched as the car was visible in my side mirror before it was gone from my eye sight, and then appeared in the rear mirror before it was gone from the side mirror. Great for road safety.

Take ten seconds next time you drive your car and fix your mirrors.

Passwords for the Manly Man 

14 April 2015

Easy to remember, hard to crack, and manly as anything. (But anyone is welcome to use them).

Best used with something like LastPass, KeePass Password Safe or 1Password.

This post inspired by Edward Snowden on Passwords.

A Cooking Show For The Rest Of Us 

19 February 2015

The Katering Show feels like a cooking show I could really sink my teeth into. If I hadn't already bitten off more of life than I can chew right now. I give it five ironic gold star stickers out of five.

SpiderOak is serious about privacy 

19 February 2015

SpiderOak is a service a bit like Dropbox, which makes it easy to backup, sync and share files. But they take data privacy so seriously, they put this prominently on their page about mobile access:

Here's the deal: when accessing your data via the SpiderOak website or on a mobile device, you must enter your password. The password will then exist in the SpiderOak server memory for the duration of your browsing session. For this amount of time, your password is stored in encrypted memory and never written to an unencrypted disk. The moment your browsing session ends, your password is destroyed and no further trace is left.

The instance above represents the only situation where your data could potentially be readable to someone with access to the SpiderOak servers. That said, no one except a select number of SpiderOak employees will ever have access to the SpiderOak servers. To fully retain our 'zero-knowledge' privacy, we recommend you always access your data via the SpiderOak desktop application, which downloads your data before decrypting it locally.

I think they've done just about as well as you could possibly expect to explain this issue clearly, while also providing the features that everyone wants.

How the sun sees you 

26 September 2014

Wow. Just wow. I'm putting some sunscreen on.

Fame and the Internet 

25 September 2014

I didn't know anything about the events mentioned in this video, but you don't have to in order to understand the point. And the core message is very much worth understanding, and spreading, because as the video explains, the very nature of fame has changed considerably due to the Internet, and none of us knows if it might happen to us.

What is Functional Programming? 

19 September 2014

Usually, when you ask a question, [if] everybody tells you something different, you know it's maybe not that easy.

Manuel Chakravarty presented an excellent talk last month about Functional Programming, and how the Swift language fits into that world.

I've been unable to fully wrap my head around this topic for years, and I felt like I was missing something. So I was extremely grateful to have someone with his expertise explain that there is no single definition of what Functional Programming is, but rather that there are a whole set of techniques and technologies that can be useful to improve software quality. And after talking to him last night, I'm going to try learning a little Haskell, just to start rewiring my brain so I can write better software myself.

David Mitchell on Machines 

05 September 2014

And it's also why machines now... pause.

It's not all progress, then.