Django-Postgres: length of CharField as primary key

Question

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

@WillemVanOnsem please see the edited version of my question. It is when I add into the many-to-many field. — Galil, Jun 08 '18 at 14:41
@houzayfarifai please see the edited version of my question. There is a many-to-many field — Galil, Jun 08 '18 at 14:42
Please include the full traceback as well as the full content of the model `GenericRoom` and `Room` — trinchet, Jun 11 '18 at 17:37
It looks your model and DB table are not synced somehow, note the field `type` is 50 chars length in the model but 100 in postgres? — trinchet, Jun 11 '18 at 20:40
@trinchet, you were right. I dropped the table and recreated it and now it works. — Galil, Jun 12 '18 at 14:33

SpiXel · Accepted Answer · 2018-06-14T08:27:09.913

Different database backends have different limitations on how long strings in an indexed column can be.

For postgres, you can find the limitations here (Which is a string of length 2000)

For mysql, you can find the limitations here [And an SO post explaining it better here, which is a string of length 255)

So 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).

I 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).

I'd suggest you look at your migrations and double check them, also maybe re-creating the table would work, maybe something went wrong there.

Hope it helps maybe.

score 1 · Answer 2 · answered Jun 13 '18 at 16:58

It is never a good idea using long varchars as primary key. It's a better practice to use a serial value as pk.

But, 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).

score 0 · Answer 3 · answered Jun 14 '18 at 11:42

0

Don't use CharFields as Primary Keys, better use default pk or uuid

import uuid
class YourModel(model.Model):
    uuid = models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False)

answered Jun 14 '18 at 11:42

nrgx

325
3
13

score 0 · Answer 4 · answered Jun 14 '18 at 22:17

I think problem is with type column/field. In PG there is 100, while your model has 50 - as max size.

Please go here to use right for you method to see SQL queries. In general, this kind of problem comes from complexity. Divide, slice it till you have small enough piece to nail the issue. Proposal is to see exact queries - that way you can see if problem is at DB, or with app / model. Then you can try smaller model, or smaller table like two columns - key + something, no inheritance... Anyway - my bet - model. :)

Django-Postgres: length of CharField as primary key

4 Answers4