Allocate ranges of primary key integers

Question

Given RangeA of auto-incrementing primary key integers (1 to 32767) and RangeB (32768 to 2147483647). If a condition is true, save an object with a primary key assigned in RangeA, else save it in RangeB.

The admin (me) would be the only user saving to RangeA. If the above is not possible: It would be not ideal but still usable if Django always saved in RangeB and it required going into shell to save in RangeA.

How can this be done using Django and Postgres?

e4c5 · Accepted Answer · 2017-01-08T04:29:37.697

Quite possible. First thing is to change your model so that it doesn't use the standard AutoField as the primary key

class MyModel(models.Model):
    id  = models.IntegerField(primary_key=True)

Then you need to connect to postgresql and create two different sequences.

CREATE SEQUENCE small START 1;
CREATE SEQUENCE big START 32768;

Instead of typing that into the PSQL console, you might also consider editing the django migration (using a RunSQL directive) to create create these.

Next step is to override the save method

def save(self,*args, **kwargs)
    if not self.id :
        cursor = connection.cursor()
        if small condition:
            cursor.execute("select nextval('small')")
        else:
            cursor.execute("select nextval('big')")

        self.id = cursor.fetchone()[0]

    super(MyModel,self).save(*args,**kwargs)

Alternative to overriding the save method is to create a postgresql BEFORE INSERT Trigger. The round trip to get the nextval isn't very costly but if this is a concern creating a trigger is the option to choose. In that case you don't need to over ride the save method but you have to implement the same logic inside the trigger.

Allocate ranges of primary key integers

1 Answers1