I have trouble inserting UUID values into a UUID-array column in Postgres.
Since psycopg2 doesn't automatically convert Python UUID values to Postgres UUID, I use something like str(my_id)
However this is the first time I am using any array-type column. The error I get is:
psycopg2.ProgrammingError: column "items" is of type uuid[] but expression is of type text[]
LINE 3: ...0', NULL, '01adae1e-e2cf-11e4-b773-ac8112c8e1fb', ARRAY['01a...
^
HINT: You will need to rewrite or cast the expression.
I've googled this error and found some (eg bug 2279, bug 2277) Postgres bug reports that hint at incomplete/missing implementation of array features but I don't know whether these relate to my problem. These however seem to have been resolved. FWIW I'm using Postgres 9.3.6
To debug this I created a small test table, eg:
testdb=# \d test_table
Table "public.test_table"
Column | Type | Modifiers
-----------+---------+-----------
key | integer |
item_list | uuid[] |
And the output from my talkative test:
DEBUGGING:
Statement: INSERT INTO test_table
("item_list", "key")
VALUES (%s, %s);
Arguments: [['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2f28dd7e-e34b-11e4-9b5e-2c768ada7efb'], 1]
Active Role: test1
Mogrified SQL Expression:
INSERT INTO test_table
("item_list", "key")
VALUES (ARRAY['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2f28dd7e-e34b-11e4-9b5e-2c768ada7efb'], 1);
Exception ProgrammingError occured. Arguments:
('column "item_list" is of type uuid[] but expression is of type text[]\nLINE 3: VALUES (ARRAY[\'2f286cea-e34b-11e4-9b5e-2c768ada7efb\', \'2...\n ^\nHINT: You will need to rewrite or cast the expression.\n',)
* * * * WARNING: Command Failure on Running Statement
INSERT INTO test_table
("item_list", "key")
VALUES (%s, %s);!
ERROR: column "item_list" is of type uuid[] but expression is of type text[]
LINE 3: VALUES (ARRAY['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2...
^
HINT: You will need to rewrite or cast the expression.
None
Traceback (most recent call last):
File "db-test.py", line 214, in <module>
db_exec(cursor, stmt, args, debug=True, re_raise=True)
File "db-test.py", line 62, in db_exec
cur.execute(stmt, args)
psycopg2.ProgrammingError: column "item_list" is of type uuid[] but expression is of type text[]
LINE 3: VALUES (ARRAY['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2...
^
HINT: You will need to rewrite or cast the expression.
The only other thing I've tried is to use uuid raw but I get the expected "Can't adapt" error.
The exception DOES occur one line earlier in my test program, eg
DEBUGGING:
Statement: INSERT INTO test_table
("item_list", "key")
VALUES (%s, %s);
Arguments: [[UUID('2bfaf276-e34c-11e4-8af5-2c768ada7efb'), UUID('2bfb6d82-e34c-11e4-8af5-2c768ada7efb')], 1]
Active Role: test1
Mogrified SQL Expression:
Exception ProgrammingError occured. Arguments:
("can't adapt type 'UUID'",)
* * * * WARNING: Command Failure on Running Statement
INSERT INTO test_table
("item_list", "key")
VALUES (%s, %s);!
None
None
Traceback (most recent call last):
File "db-test.py", line 214, in <module>
db_exec(cursor, stmt, args, debug=True, re_raise=True)
File "db-test.py", line 61, in db_exec
print cur.mogrify(stmt, args)
psycopg2.ProgrammingError: can't adapt type 'UUID'
The relevant part of the test program looks like this:
46 def db_exec(cur, stmt, args=None, re_raise=True, debug=False, assume_role=None):
47 if (cur is None) or (cur is False):
48 print "ERROR : DB Connection/Cursor is not open for commands"
49 return cur
50 if (debug is True):
51 print "DEBUGGING:"
52 print "Statement: ", stmt
53 if args is not None:
54 print "Arguments: ", args
55 try:
56 if assume_role is not None:
57 cur.execute('SET ROLE "%s";' % assume_role)
58 if debug is True:
59 print "Active Role:", GetCurrentRole(cur)
60 print "Mogrified SQL Expression:"
61 print cur.mogrify(stmt, args)
62 cur.execute(stmt, args)
[...snip...]
I have seen that there exists a way to add custom "autmatic" type conversions for Python <--> Postgres and wonder whether the solution may lie in there... but I don't know how to do that (yet)
