Wednesday, August 3, 2011

Django 1.3 model ID not showing up after .save() with PostgreSQL

So there was an interesting change with how Django selects the value for an ID with the PostgreSQL back end between 1.1 and 1.3 (or perhaps 1.2, I'm not sure.)

The function to get the ID of the newly saved model used to read:

def last_insert_id(self, cursor, table_name, pk_name):
cursor.execute("SELECT CURRVAL('\"%s_%s_seq\"')" % (table_name, pk_name))
return cursor.fetchone()[0]

But in 1.3 (at least), it reads:

def last_insert_id(self, cursor, table_name, pk_name):
# Use pg_get_serial_sequence to get the underlying sequence name
# from the table name and column name (available since PostgreSQL 8)
cursor.execute("SELECT CURRVAL(pg_get_serial_sequence('%s','%s'))" % (
self.quote_name(table_name), pk_name))
return cursor.fetchone()[0]

The difference is that 1.3 asks PostgreSQL what the name of the sequence is based on the table+pk name. The name is a little misleading as the PostgreSQL documentation indicates. It really should be like "pg_get_owned_sequence" because that's what it's asking: what is the sequence owned by this column?

It turns out if you have a pk that was created as an INTEGER and then assigned a default value of the next value of a sequence... you still don't quite have the same thing as a pk defined as serial because the column won't own the sequence.

You can rectify the situation thus:


And that will fix it. Then the pg_get_serial_sequence call will not return NULL, and your model will get an ID.

This is all something you can figure out with the documentation on the web, but I figured I'd put it here to maybe help someone, somewhere.