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