id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
18833	Model returns incorrect values from Postgres DB View	paul.ortman@…	nobody	"This is essentially the same report as [http://stackoverflow.com/questions/11781867/django-queryset-returns-wrong-values-from-postgresql-view]

I have a database view that returns aggregate data from table.


{{{
=> \d+ valid_logins_dow_popularity
        View ""public.valid_logins_dow_popularity""
   Column   |  Type   | Modifiers | Storage | Description
------------+---------+-----------+---------+-------------
 logins_avg | integer |           | plain   |
 dow        | integer |           | plain   |
View definition:
 WITH by_dow AS (
         SELECT valid_logins_over_time.count, date_part('dow'::text, valid_logins_over_time.date) AS dow
           FROM valid_logins_over_time
        )
 SELECT avg(by_dow.count)::integer AS logins_avg, by_dow.dow::integer AS dow
   FROM by_dow
  GROUP BY by_dow.dow;
}}}



I have a Django model that uses that view as its data source


{{{
from django.db import models

# Create your models here.
class ValidLoginsDowPopularity(models.Model):
    class Meta:
        db_table = 'valid_logins_dow_popularity'
        managed = False

    logins_avg = models.IntegerField(db_column='logins_avg')

    # Day of Week (dow)
    dow = models.IntegerField(db_column='dow',
                              primary_key=True)

    def __unicode__(self):
        return u""%d : "" % (self.dow, self.logins_avg )
}}}


I have a test program that simply prints the values returned via the model


{{{
$ cat test_model.py
from core.models import *

v = ValidLoginsDowPopularity.objects.all().order_by('dow')
for i in v:
    print ""logins_avg : %d | dow : %d"" % (i.logins_avg, i.dow)
}}}

and the values printed


{{{
$ python test_model.py
logins_avg : 45 | dow : 0
logins_avg : 137 | dow : 1
logins_avg : 141 | dow : 2
logins_avg : 140 | dow : 3
logins_avg : 142 | dow : 4
logins_avg : 114 | dow : 5
logins_avg : 45 | dow : 6
}}}

Unfortunately, these values are not the same as returned directly from the database.  I cannot tell if there is a pattern to how they are different, but they vary nevertheless.  Here is a direct query result.


{{{
=> select * from valid_logins_dow_popularity order by dow;
 logins_avg | dow
------------+-----
         51 |   0
        153 |   1
        142 |   2
        145 |   3
        142 |   4
        102 |   5
         45 |   6
(7 rows)
}}}


Finally, here is a test in pure python using the psycopg2 driver (same as in use in Django) that gets the correct data and prints it as expected.

{{{
$ cat test_psycopg2.py
import psycopg2

def main():
    conn_string = ""dbname='auser' user='auser'""

    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()

    sql = ""select * from valid_logins_dow_popularity order by dow""
    cursor.execute(sql)

    for rec in cursor.fetchall():
        print rec

if __name__ == '__main__':
    main()
}}}

and the correct data

{{{
$ python test_psycopg2.py
(51, 0)
(153, 1)
(142, 2)
(145, 3)
(142, 4)
(102, 5)
(45, 6)
}}}

finally, here is the critical version info

{{{
Django==1.4
psycopg2==2.4.5
}}}"	Bug	closed	Database layer (models, ORM)	1.4	Normal	invalid			Unreviewed	0	0	0	0	0	0