Django ProgrammingError in Fields on PostgreSQL

Question

models.py

class Stop(models.Model):
    idn = models.PositiveIntegerField(primary_key=True, unique=True)
    label = models.CharField(null=False, blank=False, max_length=512)
    coor_x = models.FloatField()
    coor_y = models.FloatField()
    buses = models.ManyToManyField(Bus)
    latest_query_datetime = models.DateTimeField(default=datetime(2000, 1, 1, 0, 0, 0))
    latest_query_data = JSONField(default={})

    class Meta:
    ordering = ["label"]

    def __str__(self):
    return self.label

When I run:

python3 manage.py makemigrations && python3 manage.py migrate

It raises a ProgrammingError saying that jsonb datatype does not exist:

Migrations for 'rest':
  0007_auto_20160612_1301.py:
    - Alter field latest_query_data on stop
Operations to perform:
  Apply all migrations: contenttypes, rest, auth, sessions, admin
Running migrations:
  Rendering model states... DONE
  Applying rest.0005_auto_20160612_1237...Traceback (most recent call last):
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: type "jsonb" does not exist
LINE 1: ... TABLE "rest_stop" ADD COLUMN "latest_query_data" jsonb DEFA...
                                                         ^


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

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/core/management/__init__.py", line 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/core/management/base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 200, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/migrations/executor.py", line 92, in migrate
    self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/migrations/executor.py", line 121, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/migrations/executor.py", line 198, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/migrations/migration.py", line 123, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/migrations/operations/fields.py", line 62, in database_forwards
    field,
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 396, in add_field
    self.execute(sql, params)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 110, in execute
    cursor.execute(sql, params)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/erayerdin/.venv/eshot-api/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: type "jsonb" does not exist
LINE 1: ... TABLE "rest_stop" ADD COLUMN "latest_query_data" jsonb DEFA...

I use PostgreSQL to use JSONField and update it when a user requests a view. If I do not use default={}, it tells me to make one.

Further Analysis

I changed latest_query_data field to TextField so that I can store as string and convert to dict when I need. However, this also raised the same error.

---

Environment

• django 1.9.6
• psycopg 2.6.1

Answer 1

With older versions of PostgreSQL for example "PostgreSQL9.2.x",

We can use an alternative as

from jsonfield import JSONField

instead of:

from django.contrib.postgres.fields import JSONField

for example:

from django.db import models
from jsonfield import JSONField

class MyModel(models.Model):
      json = JSONField()

Can install as:

 pip install jsonfield

Check this out: Add support for PostgreSQL 9.2+'s native json type. #32

This solution is especially good for some limited edition versions. For example, on VPS & Cpanel that supports postgresql9.2 by default

more details!, see"rpkilby-jsonfield"

Answer 2

Based on the Anonymous comment, I found the following to work:

from django.contrib.postgres import fields

class OldJSONField(fields.JSONField):
    def db_type(self, connection):
        return 'json'

class Stop(models.Model):
    ...
    latest_query_data = OldJSONField(default=dict)
...

Answer 3

If you are getting this error and you have installed Postgres > 9.4 then I would check that you aren't connecting to an older version of Postgres that is also installed on your instance.

To confirm what you are connecting to from Django you can use psycopg2 from the shell:

import psycopg2
conn = psycopg2.connect("dbname=<your database> user=<your user> password=<your password>")
cur = conn.cursor()
cur.execute("SELECT Version();")
cur.fetchone()

Make sure the version here is > 9.4. If not you probably have a couple of versions installed and your service configuration is pointing at the other version.

Answer 4

According to the Django docs, JSONField requires PostgreSQL ≥ 9.4 and Psycopg2 ≥ 2.5.4

What PostgreSQL version are you using?

See https://docs.djangoproject.com/en/dev/ref/contrib/postgres/fields/#django.contrib.postgres.fields.JSONField

Ubuntu 14.04 repositories contain only 9.3 version. You can review this to upgrade your version.

Answer 5

So, it seems a bug on psycopg2 or django, I will post an issue on both repositories. This is how I've solved (at least, ProgrammingError) the problem.

1. Change JSONField to TextField.

2. Flush your database.

   Beware! This operation will erase all the data but the structure in your database.

3. Remove all migrations folder in all of your apps.

4. Run python3 manage.py makemigrations && python3 manage.py migrate in all of your apps.

5. Run python manage.py makemigrations <appname> && python3 manage.py migrate <appname> for each app you have.

6. Use built-in json module to convert between str and dict.

However, remember, this solution requires so much effort if you want to filter a QuerySet of a model. I do not recommend it, but there was no other solution to get rid of this error and all I needed to do was to save data and represent it.

! This answer will be accepted as default if there will not occur any other better solution in 48 hours.