This project is archived and is in readonly mode.

#231 ✓invalid
Psycopg website

I have bug with array of composite types

Reported by Psycopg website | August 22nd, 2014 @ 06:42 AM

Submitted by: Nikita Kuznetsov

import psycopg2
import psycopg2.extras


conn = psycopg2.connect("dbname=test user=y")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS test_comp_table;")
cur.execute("DROP TYPE IF EXISTS test_comp_type;")
cur.execute("CREATE TYPE test_comp_type AS (first text, second text);")
psycopg2.extras.register_composite("test_comp_type", conn, globally=True)
cur.execute("CREATE TABLE test_comp_table (data test_comp_type[]);")
try:
    cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
                dict(data=[("first", "second")]))
except:
    print(cur.query)
    raise

"""
b"INSERT INTO test_comp_table VALUES (ARRAY[('first', 'second')])"
Traceback (most recent call last):
  File "../common/test.py", line 14, in <module>
    dict(data=[("first", "second")]))
psycopg2.ProgrammingError: column "data" is of type test_comp_type[] but expression is of type record[]
LINE 1: INSERT INTO test_comp_table VALUES (ARRAY[('first', 'second'...
                                            ^
HINT:  You will need to rewrite or cast the expression.
"""

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo August 22nd, 2014 @ 01:05 PM

    • State changed from “new” to “invalid”
    • Tag changed from compositetype, rel-2.5.3 to compositetype, question, rel-2.5.3

    Try adding an explicit cast after your placeholder specifying that the type you are passing is an array of test_comp_type. This should work:

    cur.execute("INSERT INTO test_comp_table VALUES (%(data)s::test_comp_type[])",
                dict(data=[("first", "second")]))
    
  • Nikita Kuznetsov

    Nikita Kuznetsov August 22nd, 2014 @ 03:32 PM

    No, I want autocast, because I use sqlalchemy and I work througth sqlalchemy interface.

  • Daniele Varrazzo

    Daniele Varrazzo August 22nd, 2014 @ 04:56 PM

    In this case you should create your data as a specific python type, it cannot be a generic tuple, and register a typecaster for it. For instance:

    Comp = namedtuple('Comp', 'first second')
    
    class CompAdapter:
        def __init__(self, x):
            self.adapted = psycopg2.extensions.SQL_IN(x)
        def prepare(self, conn):
            self.adapted.prepare(conn)
        def getquoted(self):
            return self.adapted.getquoted() + '::test_comp_type'
    
    psycopg2.extensions.register_adapter(Comp, CompAdapter)
    
    # this now works
    cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
        {'data': [ Comp('a', 'b'), Comp('c', 'd') ]})
    

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