This project is archived and is in readonly mode.

#72 ✓invalid
Psycopg website

ARRAY could not convert type double precision[] to numeric[]

Reported by Psycopg website | October 13th, 2011 @ 03:24 PM

Submitted by: oscar martinez

Hi, I detected that when having a 3D array of double precesion and some of the values are nan it crashes.

I paste the test I did (I created a table with only one column which is a 3d array)

In [1]: import psycopg2

In [2]: connection = psycopg2.connect("dbname='martinez' user='postgres' host='node079'")

In [3]: cursor = connection.cursor()

In [4]: values = [[[0.,2.],[0.,1.],[0.,0.]],]

In [5]: cursor.execute('INSERT INTO PROBA (col) VALUES (%s)', [values,])

In [6]: values = [[[0.,2.],[float('NaN'),1.],[0.,0.]],]

In [7]: cursor.execute('INSERT INTO PROBA (col) VALUES (%s)', [values,])

ProgrammingError Traceback (most recent call last)

/home/users/martinez/ in ()

ProgrammingError: ARRAY could not convert type double precision[] to numeric[]

In [8]: psycopg2.version
Out[8]: '2.4 (dt dec pq3 ext)'

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo October 13th, 2011 @ 03:49 PM

    Cannot reproduce the issue: adaptation seems performed ok. What is the definition of the table?

    With the lack of such definition I can only try a cast, and it seems working fine:

    In [6]: cur.execute("select %s::numeric[]", [values])
    
    In [7]: cur.fetchone()
    Out[7]: 
    ([[[Decimal('0.0'), Decimal('2.0')],
       [Decimal('NaN'), Decimal('1.0')],
       [Decimal('0.0'), Decimal('0.0')]]],)
    
    In [8]: cur.execute("select %s::double precision[]", [values])
    
    In [9]: cur.fetchone()
    Out[9]: ([[[0.0, 2.0], [nan, 1.0], [0.0, 0.0]]],)
    

    Please post the definition of the table and the postgres version you are using. I suspect it is a Postgres limitation, not a psycopg one. psycopg version tested is 2.4.2 (can't remember about any fix performed between 2.4.0 and 2.4.2 that could influence this result).

  • Oscar Martinez

    Oscar Martinez October 14th, 2011 @ 12:43 PM

    Hi,

    Sorry for the lack of info. I post now the definition of the table. I have asked the admin to update the psycopg2 to the last one (2.4.2) but I still get the same error. BTW, teh postgres version is 8.1.22

    
    In [1]: import psycopg2
    
    In [2]: connection = psycopg2.connect("dbname='martinez' user='postgres' host='node079'")
    
    In [3]: cursor = connection.cursor()
    
    In [4]: cursor.execute('CREATE TABLE PROBA (col DOUBLE PRECISION[][][])')
    
    In [5]: connection.commit()
    
    In [6]: values = [[[0.,2.],[0.,1.],[0.,0.]],]
    
    In [7]: cursor.execute('INSERT INTO PROBA (col) VALUES (%s)', [values,])
    
    In [8]: values = [[[0.,2.],[float('NaN'),1.],[0.,0.]],]
    
    In [9]: cursor.execute('INSERT INTO PROBA (col) VALUES (%s)', [values,])
    ---------------------------------------------------------------------------
    ProgrammingError                          Traceback (most recent call last)
    
    /home/users/martinez/<ipython console> in <module>()
    
    ProgrammingError: ARRAY could not convert type double precision[] to numeric[]
    
    In [10]: psycopg2.__version__
    
    Out[10]: '2.4.2 (dt dec pq3 ext)'
    

    Thanks

  • Daniele Varrazzo

    Daniele Varrazzo October 14th, 2011 @ 01:13 PM

    Your problem is in postgres: probably 8.1 doesn't support NaN in numeric array. Your test works fine with postgres 9.0.

  • Oscar Martinez

    Oscar Martinez October 14th, 2011 @ 01:22 PM

    Ok, I will ask the admin to upgrade it
    Thanks very much!

  • Daniele Varrazzo

    Daniele Varrazzo October 14th, 2011 @ 09:21 PM

    • State changed from “new” to “invalid”

    I've tested with my collection of dbs and you need at least PostgreSQL 8.4.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

Pages