Home:ALL Converter>Django-Postgres: length of CharField as primary key

Django-Postgres: length of CharField as primary key

Ask Time:2018-06-08T22:11:46         Author:Galil

Json Formatter

In my Django model there is a CharField as primary key with 250 characters as max_length and a ManyToManyField:

class GenericRoom(models.Model):
    key = models.CharField(primary_key=True, max_length=250)
    name = models.CharField(max_length=500, default='')
    extension = models.IntegerField(null=True)
    owner = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, related_name="%(app_label)s_%(class)s_owner",
                              on_delete=models.CASCADE)
    moderators = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name="%(app_label)s_%(class)s_moderators",
                                        blank=True)
    type = models.CharField(max_length=50, choices=ROOM_TYPE_CHOICES, default='meeting')


class Room(GenericRoom):
    pin = models.IntegerField(null=True)

Also, in Postgres the max length of thekey column is 250 as shown below:

       Column        |          Type          | Collation | Nullable | Default 
---------------------+------------------------+-----------+----------+---------
 key                 | character varying(250) |           | not null | 

However, when I am giving the keys are longer than 50 characters and I am doing:

r=Room.objects.create(key='a_veeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeery_long_key')
r.moderators.add(User.objects.get(id=123))

I am getting:

  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.DataError: value too long for type character varying(50)


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/models/fields/related_descriptors.py", line 898, in add
    self._add_items(self.source_field_name, self.target_field_name, *objs)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/models/fields/related_descriptors.py", line 1065, in _add_items
    for obj_id in new_ids
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/models/query.py", line 466, in bulk_create
    ids = self._batched_insert(objs_without_pk, fields, batch_size)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/models/query.py", line 1142, in _batched_insert
    inserted_id = self._insert(item, fields=fields, using=self.db, return_id=True)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/models/query.py", line 1125, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1283, in execute_sql
    cursor.execute(sql, params)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/ilias/.virtualenvs/vsceneapi/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: value too long for type character varying(50)

For keys shorter than 50 everything works fine.

So there must be an implicit limit of 50 characters for the key column even though the limit seems to be 250.

But why? And how can I change this?

EDIT:

The table's description from Postgres is:

       Column        |          Type          | Collation | Nullable | Default 
---------------------+------------------------+-----------+----------+---------
 key                 | character varying(250) |           | not null | 
 owner_id            | integer                |           |          | 
 extension           | integer                |           |          | 
 name                | character varying(500) |           | not null | 
 type                | character varying(100) |           | not null | 
 pexip_conference_id | integer                |           |          | 
 pin                 | integer                |           |          | 
Indexes:
    "rooms_room_pkey" PRIMARY KEY, btree (key)
    "rooms_room_key_ca48250b_like" btree (key varchar_pattern_ops)
    "rooms_room_owner_id_13734245" btree (owner_id)
Foreign-key constraints:
    "rooms_room_owner_id_13734245_fk_users_user_id" FOREIGN KEY (owner_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "meeting_spaces_meetingspace" CONSTRAINT "meeting_spaces_meetingspace_room_id_10905900_fk" FOREIGN KEY (room_id) REFERENCES rooms_room(key) DEFERRABLE INITIALLY DEFERRED
    TABLE "meetings_meeting" CONSTRAINT "meetings_meeting_room_id_02a205a4_fk" FOREIGN KEY (room_id) REFERENCES rooms_room(key) DEFERRABLE INITIALLY DEFERRED
    TABLE "recordings_recording" CONSTRAINT "recordings_recording_room_id_799f40af_fk" FOREIGN KEY (room_id) REFERENCES rooms_room(key) DEFERRABLE INITIALLY DEFERRED
    TABLE "rooms_room_moderators" CONSTRAINT "rooms_room_moderators_room_id_abd2abc5_fk_rooms_room_key" FOREIGN KEY (room_id) REFERENCES rooms_room(key) DEFERRABLE INITIALLY DEFERRED
    TABLE "users_user" CONSTRAINT "users_user_room_id_06ed4152_fk" FOREIGN KEY (room_id) REFERENCES rooms_room(key) DEFERRABLE INITIALLY DEFERRED

Author:Galil,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/50762562/django-postgres-length-of-charfield-as-primary-key
Jair :

It is never a good idea using long varchars as primary key. \nIt's a better practice to use a serial value as pk.\n\nBut, since you wanted it, I made a quick search and found this: \"One limitation is that indexes can not be created on columns longer than about 2,000 characters\" (Found it here).",
2018-06-13T16:58:58
nrgx :

Don't use CharFields as Primary Keys, better use default pk or uuid\n\nimport uuid\nclass YourModel(model.Model):\n uuid = models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False)\n",
2018-06-14T11:42:55
SpiXel :

Different database backends have different limitations on how long strings in an indexed column can be.\n\nFor postgres, you can find the limitations here (Which is a string of length 2000)\n\nFor mysql, you can find the limitations here [And an SO post explaining it better here, which is a string of length 255)\n\nSo theoretically there should be no reason why this shouldn't work (Django also adds a limit to handle DB portability, allowing custom primary_keys, with max_length=255, but the length of your key is within that range).\n\nI suspect the reason is behind how you're managing your DB, since I see a mismatch between your model and actual DB table (key type is varchar(100) in the DB, but max_length=50 in your model).\n\nI'd suggest you look at your migrations and double check them, also maybe re-creating the table would work, maybe something went wrong there.\n\nHope it helps maybe.",
2018-06-14T08:21:48
yy