1

I have a Django app using MySQL with a medium-sized database and now I'm looking at migrating it to PostgreSQL. I've created a table and loaded the data, so now I can perform basic SQL queries.

From the Django side this has been more difficult. I get the following error message:

"ProgrammingError: relation "myapptable" does not exist
LINE 1: ..."myapptable"."name", "myapptable"."birthday" FROM "myapptabl...

I've looked at the SQL and it seems that with PostgreSQL, the ORM adds extra quotes around the table name. This isn't a problem with Django-created tables, only with "unmigrated" tables. That's the term used in the migration section of the Django 1.7 docs. I've tested this with both 1.7 and 1.6.

Here’s some simplified source code:

models.py

from django.db.models import Model, CharField, DateField, IntegerField

class MyTest(Model): 
    id = IntegerField(primary_key = True)
    name = CharField(max_length = 20)
    birthday = DateField()

    class Meta:
        app_label = 'mytest'
        db_table = 'myapptable'
        managed = False 

        def __unicode__(self):
            return self.name

PostgreSQL table

CREATE TABLE myapptable (
    id serial PRIMARY KEY NOT NULL,
    name varchar NOT NULL,
    birthday date NOT NULL
);

MySQL table

CREATE TABLE myapptable (
    id integer PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name varchar(20) NOT NULL,  
    birthday date NOT NULL
);

Django query

from datetime import date
from apps.filter.mytest.models import MyTest

qs = MyTest.objects.filter(
         birthday__gte = date(2007, 01, 01)
     ).filter(
         birthday__lte = date(2007, 12, 31)
     )

for row in qs:
    print(
        '{}, {}'.format(
            row.name,
            row.birthday
        )
    )

PostgreSQL query

test version for the psql client (this works):

SELECT name, birthday
  FROM  myapptable
  WHERE birthday
    BETWEEN      '2007-01-01 00:00:00'
      AND        '2007-12-31 00:00:00';

what the ORM generates (this generates the ProgrammingError message):

SELECT "myapptable"."id", "myapptable"."name", "myapptable"."birthday"
  FROM  "myapptable"
  WHERE "myapptable"."birthday"
    BETWEEN      '2007-01-01 00:00:00'
      AND        '2007-12-31 00:00:00';

MySQL query

test version for the MySQL client (this works):

SELECT name, birthday
    FROM myapptable
  WHERE birthday
    BETWEEN      '2007-01-01 00:00:00'
      AND        '2007-12-31 00:00:00';

what the ORM generates (this also works):

SELECT `myapptable`.`id`, `myapptable`.`name`, `myapptable`.`birthday`
  FROM `myapptable`
  WHERE (
    AND `myapptable`.`birthday`
      BETWEEN '2007-01-01 00:00:00'
        AND '2007-12-31 00:00:00');
highpost
  • 1,263
  • 2
  • 14
  • 25
  • Are your PostgreSQL query and MySQL query supposed to look exactly the same? You said earlier in your question that the PostgreSQL query has quotes around table and field names. (By the way, this is also what I get when I print the queries in my Django project but it should not have any impact. The quotes are just there to allow you to have characters in your table and field names that would *otherwise* be illegal.) And this may seem silly to ask but is there in fact a `myapptable` table in your database? Maybe a creation step was missed somewhere? – Louis Oct 02 '14 at 23:05
  • Are you sure those are the exactly correct table definitions? Show `\dt` output from `psql` please. My first guess would be that your custom-created tables weren't created in the correct database or schema. – Craig Ringer Oct 03 '14 at 00:40
  • Thanks, but I think this is more of a Django question than a PostgreSQL question. I've tested my tables and queries at the PostgreSQL level with the psql and mysql clients and they work fine. But at the Django level things are different. When I use MySQL as my DB the Django query above works. I can look at the table in SQL Developer and it's fine at that point. Whe I switch to using PostgreSQL as the DB, I can see the table in pgadmin3 and the psql client. But the django "select", which worked fine with MySQL generates an error message. – highpost Oct 03 '14 at 06:49
  • Is Django connecting to the correct PostgreSQL database? It could be that when you are testing with psql you type in the correct database name but your project's settings are wrong. If a mere visual inspection of `settings.py` won't reveal this then using `manage dbshell` to inspect what is the database that Django actually connects to should help. – Louis Oct 03 '14 at 09:29
  • Okay. I have my test program working w/ PostgreSQL, but not my real app. It was a db config error, so I think I can see where the arrow is pointing. – highpost Oct 04 '14 at 18:23
  • 1
    teachable moment: http://stackoverflow.com/questions/2878248/postgresql-naming-conventions; my table name was using camel case – highpost Oct 05 '14 at 21:44

0 Answers0