Geek Answers Handbook

Django ProgrammingError in fields on PostgreSQL

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 the jsonb data type 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 the user requests a view. If I do not use default={} , he will prompt me to make it.

Further analysis

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

Environment

  • django 1.9.6
  • psycopg 2.6.1
+5
source share
4 answers

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

What version of PostgreSQL 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 version 9.3. You can view this to update the version.

+11
source

Based on an anonymous comment, I found the following:

 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={}) ...
+1
source

If you get this error and you installed Postgres> 9.4, I would check that you are not connecting to the older version of Postgres, which is also installed on your instance.

To confirm that you are connecting 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 several versions, and your service configuration points to a different version.

+1
source

So it seems that the error is in psycopg2 or django, I will post the problem in both repositories. This is how I solved the (at least ProgrammingError ) problem.

  • Change JSONField to TextField .

  • Reset your database.

    Beware! This operation will delete all data except the structure in your database.

  • Delete the entire migrations folder in all your applications.

  • Run python3 manage.py makemigrations && python3 manage.py migrate in all your applications.
  • Run python manage.py makemigrations <appname> && python3 manage.py migrate <appname> for each of your applications.
  • Use the json built-in module to convert between str and dict .

However, remember that this solution requires a lot of effort if you want to filter QuerySet models. I do not recommend this, but there was no other solution to get rid of this error, and all I had to do was save the data and submit it.

! This answer will be accepted by default if there is no other better solution within 48 hours.

-3
source

More articles:


All Articles