primary key auto increment with PostgreSQL and non Django standard column name

[Naoko Reeves]

Hi, I have a Django newbie question.

My goal is to auto increment primary key with non Django standard column name.

We are converting from existing database and primary key schema is "tablename_key" and not "id".

I googled it and end up reaching to this ticket: https://code.djangoproject.com/ticket/13295

So I understand that there is work in progress but I wanted find work around.. 

1. My first try was to let postgres handle it.

my postgreSQL table looks like this:

CREATE TABLE poll

(

  poll_key integer NOT NULL DEFAULT nextval('poll_key_seq'::regclass),

  poll_question character varying(200) NOT NULL,

  poll_pub_date timestamp with time zone NOT NULL,

  CONSTRAINT poll_pkey PRIMARY KEY (poll_key)

)

My model look like this:

class Poll(models.Model):

    poll_key = models.IntegerField(primary_key=True)

    poll_question = models.CharField(max_length=200, default='')

    poll_pub_date = models.DateTimeField('date published', default=datetime.date.today())

    class Meta:

        db_table = u'poll'

I was hoping that with this, I could 

p = Poll(poll_question="Question 1?")

p.save()

but this fails because Django is actually sending the following statement:

INSERT INTO "poll" ("poll_key", "poll_question", "poll_pub_date") 

VALUES (NULL, 'Question 1?', NULL)

2. My Second attempt is then to add default to model

Created a function to return sequence value

from django.db import connection

def c_get_next_key(seq_name):

    """ return next value of sequence """

    c = connection.cursor()

    c.execute("SELECT nextval('%s')" % seq_name)

    row = c.fetchone()

    return int(row[0])

Calling like below works just fine. Everytime I call it, I get new number.

c_get_next_key('poll_key_seq')

Then I modify Poll_key in Poll model as follows:

Poll_key = models.IntegerField(primary_key=True, default=c_get_next_key('poll_key_seq'))

I went to Django Shell and created first record.

p1 = Poll(poll_question="P1")

p1.poll_key

# this will return let's say 37

p1.save()

# saves just fine.

# instantiating new object

p2 = Poll(poll_question="P2")

p2.poll_key

# this also return 37.

I know I must be doing something wrong... but could you pint me to right direction? I am expecting p2.poll_key to return 38.

Thank you very much for your help in advance.

Naoko

[Casey Greene]

Doesn't autofield with primary_key=True handle this for you (instead of
making it an IntegerField):

https://docs.djangoproject.com/en/1.3/ref/models/fields/#autofield

Hope this helps!
Casey

> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To post to this group, send email to django...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.

[Naoko Reeves]

Casey,
Thank you for your help! As Casey suggested by changing the column
definition of model to:

poll_key = models.AutoField(primary_key=True)

Now create INSERT statement as follows:
INSERT INTO "poll" ("poll_question", "poll_pub_date") VALUES ('Question!',
'2011-05-27 00:00:00')

With PostgreSQL Schema:

poll_key integer NOT NULL DEFAULT nextval('poll_key_seq'::regclass),

It achieve the goal.
Now question: Documentation indicates that " This is an auto-incrementing
primary key.". This suggest me that Django will create INSERT statement with
poll_key with next sequence value. Am I understanding incorrectly?

Thank you again for your advice in advance.

[Naoko Reeves]

I see if column is set to AutoField then Django won't send INSERT poll_key
as null.
Now my problem is that it doesn't return newly assigned primary key value
for me if primary key name is _key instead of _id
It looks as if sequence name is not understood correctly.
Could you tell me if
1) I need to change sequence name to something else? Currently it is
poll_key_seq
2) Is there a way to specify the sequence name?

class Poll(models.Model):
poll_key = models.AutoField(primary_key=True)

poll_question = models.CharField(max_length=200, default='')

class Poll2(models.Model):
poll2_id = models.AutoField(primary_key=True)
poll2_question = models.CharField(max_length=200, default='')

>>> from mysite.polls.models import Poll2
>>> p3 = Poll2(poll2_question='3')
>>> p3.save()
>>> p3.pk
2L
>>> p4 = Poll2(poll2_question='4')
>>> p4.save()
>>> p4.pk
3L
>>> from mysite.polls.models import Poll
>>> p5 = Poll(poll_question='5')
>>> p5.save()
>>> print p5.pk
None

On 5/27/11 5:31 PM, "Casey Greene" <csgr...@princeton.edu> wrote:

[Malcolm Box]

You need to tell django what the db column name for your pollkey field is. Look at the dbname field option in the docs.

Sent from my iPhone, please excuse any typos

[Naoko Reeves]

Malcolm, Thank you for your advice!
I changed my model as follows:
poll_key = models.AutoField(primary_key=True, db_column='poll_key')
However, the result remain the same as shown below. If you could point me
out to right direction again, I would appreciate. Thank you very much for
your time.

>>> from mysite.polls.models import Poll2
>>> p3 = Poll2(poll2_question='3')
>>> p3.save()
>>> p3.pk

4L

>>> from mysite.polls.models import Poll
>>> p5 = Poll(poll_question='5')
>>> p5.save()
>>> print p5.pk
None

[Naoko Reeves]

I was missing this from schema:
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Now everything is happy. Thank you!

[Aditya Sangam]

Naoko

I ma facing the problem of updating primary key automatically in django with postgresql database 

Could you please suggest what need to be done.

Do we need to create a sequence for the table to achieve this.

Hoping for your reply