This project is archived and is in readonly mode.

I have bug with array of composite types

#231

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.
"""

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

Activity

Daniele Varrazzo
- 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")]))
```
August 22nd, 2014 @ 01:05 PM
Nikita Kuznetsov
No, I want autocast, because I use sqlalchemy and I work througth sqlalchemy interface.

August 22nd, 2014 @ 03:32 PM

Daniele Varrazzo

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') ]})

August 22nd, 2014 @ 04:56 PM