1

we have a column in the PostgreSQL table, the column type is float(8) and in 1 record we have a value of 21.291400909423828 under that column. When we are reading from python using psycopg2 with a simple SQL query, we are getting the record but the column value is coming as 21.2914009094238 i.e., truncated by the last 2 decimal values/positions.

I have tried the below approach to customize the psycopg2 reading:

    psycopg2.extras.register_uuid()
    DEC2FLOAT = psycopg2.extensions.new_type(
        psycopg2.extensions.FLOAT.values,
        "DEC2FLOAT",
        lambda value, curs: Decimal(value) if value is not None else None)
    psycopg2.extensions.register_type(DEC2FLOAT)

But didn't get succeded.

The code that I'm using :

connection_pool = pool.SimpleConnectionPool(1, 10, 
                   database=connection_string['db_name'],                                                
                   user=connection_string['db_user'],                                               
                   password=connection_string['db_password'],                                             
                   host=connection_string['db_endpoint'], port=5432, 
                   **keepalive_kwargs)

cursor = connection_object.cursor()
cursor.execute(query, params)
print(query, params)

records = cursor.fetchall()

It would be helpful if anyone shares their experience and thoughts on this.

Jagadeesh
  • 119
  • 2
  • 7
  • 4
    What version of Postgres? In `postgresql.conf` what is [extra_float_digits](https://www.postgresql.org/docs/12/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS) set to? I'm going to guess 0. `set extra_float_digits = 0; select 21.291400909423828::float8; 21.2914009094238 set extra_float_digits = 1; select 21.291400909423828::float8; 21.291400909423828` Add answers as update to your question. – Adrian Klaver Aug 16 '21 at 17:01
  • we're using AWS RDS, where we can see this extra_float_digits is set or not? I've checked in parameter groups but didn't found anything. – Jagadeesh Aug 17 '21 at 08:59
  • According to this [Parameters](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.Parameters) it is there. If you connect using `psql` then `SHOW extra_float_digits` should return the current value. – Adrian Klaver Aug 17 '21 at 14:08
  • @AdrianKlaver, our database is already having extra_float_digits set to 3. – Jagadeesh Aug 17 '21 at 14:20
  • The only way I can replicate using your typecast above is to do: `cur.execute("select 21.291400909423828::float8::numeric") cur.fetchone() (Decimal('21.2914009094238'),) ` and that is because: `select 21.291400909423828::float8::numeric; 21.2914009094238`. Are you doing a `numeric` cast somewhere in the query. Otherwise you are going to have to step through the process to see where the two digits go missing. – Adrian Klaver Aug 17 '21 at 14:51
  • uhh no, I am not using numeric on the query, and yes maybe I should debug the cur.execute call. – Jagadeesh Aug 17 '21 at 15:37
  • we're using cur.fetchall() and we're not getting Decimal values, we're directly getting float type, any idea why so? – Jagadeesh Aug 17 '21 at 15:44
  • Without a full example of the code you are using, then there is really no way to say what is happening. If you do provide an example add it as update to your question. – Adrian Klaver Aug 17 '21 at 16:56
  • @AdrianKlaver, I've updated the question with the code I'm using, but it is none less than the tutorial code every say about python-psycopg2 connection. – Jagadeesh Aug 19 '21 at 17:06
  • Except you left out the important parts, that is what `query` and `params` are? Also I am not seeing anywhere where you register your type? – Adrian Klaver Aug 19 '21 at 19:03
  • @AdrianKlaver, we found the issue i.e., python-PostgreSQL data mappings, double-precision type PostgreSQL columns convert to float in Python and numeric columns gets converted to Decimal. so we tried storing the same values in the newly created test column of type numeric and then when we read from Python the whole value with all decimal values are coming in python and it is of type Decimal in Python. https://www.postgresql.org/docs/9.4/plpython-data.html – Jagadeesh Aug 23 '21 at 09:17
  • Now we're facing issue on DB column datatype alteration - https://stackoverflow.com/questions/68952800/how-to-convert-postgres-double-precision-to-numeric-without-data-loss-trunca Please provide your suggestions. – Jagadeesh Aug 27 '21 at 11:41

0 Answers0