The Most Ancient Music Known To Man 

It’s very cool that we can listen to this ancient tune.

I Could Care Less 

I want you to feel less alone.

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

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 = [
            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, [
                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 $$
    table_names text[];
    this_table_name text;
    the_constraint_name text;
    index_names record;


SELECT array['table1',
    INTO table_names;

FOREACH this_table_name IN array table_names
    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
    HAVING count(*) = 1;
    if the_constraint_name is not NULL then
        RAISE notice 'alter table % drop constraint %',
        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,
        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;',
    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


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 

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 

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 

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 

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 

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 

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

More posts