Search code examples
postgresqlroundingpsycopg2truncated

psycopg2 rounds my float in SELECT statement

I'm writing a quick script to retrieve values from my database using psycopg2. Everything seemed to work fine until I verified the values in my output. I was astonished to realise they were rounded up. I double-checked the values stored and they are correct. So why it this query

cursor = connection.cursor()
cursor.execute('SELECT val, val+0 FROM mytable;')
rows = cursor.fetchall()
print(rows[0])

returning

(1860500.0, 1860496.0)
 ^ rounded? ^ correct

What am I doing wrong? Note: column val is defined as

ALTER TABLE public.mytable ADD COLUMN val real;
Solution

  • Try SET extra_float_digits=3 and see if that changes things.

    The second value is converted to numeric internally because that is the “least common denominator” for real and integer, and numeric is not rounded.

    You should use double precision rather than real.