zzzeek/sqlalchemy — pull request #68 by kdeldycke (2014-02-06 10:53:32+00:00)
Title: No support of UUID arrays for PostgreSQL.

This PR add a unit test to demonstrate the lack of support of UUID arrays for PostgreSQL.

This unit test produce the following error:
```bash
./sqla_nose.py --dburi=postgresql://localhost/sql_unittests test.dialect.postgresql.test_types:UUIDTest
nose.config: INFO: Set working dir to /Users/kdeldycke/virtualenvs/sqla/sqlalchemy/test
nose.config: INFO: Working directory /Users/kdeldycke/virtualenvs/sqla/sqlalchemy/test is a package; adding to sys.path
nose.config: INFO: Ignoring files matching ['^\\.', '^_', '^setup\\.py$']
nose.config: INFO: Excluding tests matching ['^examples']
test.dialect.postgresql.test_types.UUIDTest.test_no_uuid_available ... ok
test.dialect.postgresql.test_types.UUIDTest.test_uuid_array ... ERROR
test.dialect.postgresql.test_types.UUIDTest.test_uuid_string ... ok
test.dialect.postgresql.test_types.UUIDTest.test_uuid_uuid ... ok

======================================================================
ERROR: test.dialect.postgresql.test_types.UUIDTest.test_uuid_array
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/kdeldycke/virtualenvs/sqla/lib/python2.7/site-packages/nose/case.py", line 197, in runTest
self.test(*self.arg)
File "", line 2, in test_uuid_array
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/testing/exclusions.py", line 80, in decorate
return fn(*args, **kw)
File "", line 2, in test_uuid_array
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/testing/exclusions.py", line 80, in decorate
return fn(*args, **kw)
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/test/dialect/postgresql/test_types.py", line 949, in test_uuid_array
[str(uuid.uuid4()), str(uuid.uuid4())],
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/test/dialect/postgresql/test_types.py", line 973, in _test_round_trip
self.conn.execute(utable.insert(), {'data':value1})
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/engine/base.py", line 717, in execute
return meth(self, multiparams, params)
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/engine/base.py", line 814, in _execute_clauseelement
compiled_sql, distilled_params
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/engine/base.py", line 927, in _execute_context
context)
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
exc_info
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/util/compat.py", line 185, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/engine/base.py", line 920, in _execute_context
context)
File "/Users/kdeldycke/virtualenvs/sqla/sqlalchemy/./lib/sqlalchemy/engine/default.py", line 425, in do_execute
cursor.execute(statement, parameters)
ProgrammingError: (ProgrammingError) column "data" is of type uuid[] but expression is of type text[]
LINE 1: INSERT INTO utable (data) VALUES (ARRAY['22b2b994-0d2e-40f0-...
^
HINT: You will need to rewrite or cast the expression.
'INSERT INTO utable (data) VALUES (%(data)s)' {'data': ['22b2b994-0d2e-40f0-ae57-7874ee7fa522', '0c35c1a4-3027-411d-ac90-0e8ad64ba272']}

----------------------------------------------------------------------
Ran 4 tests in 0.019s

FAILED (errors=1)
```

The root cause is that UUID arrays seems to have a dedicated syntax, using curly braces instead of the `ARRAY` keyword.

SQLAlchemy produce the following wrong query:
```sql
INSERT INTO utable (data) VALUES (ARRAY['22b2b994-0d2e-40f0-ae57-7874ee7fa522', '0c35c1a4-3027-411d-ac90-0e8ad64ba272']);
```

But the working query should be expressed as:
```sql
INSERT INTO utable (data) VALUES ('{22b2b994-0d2e-40f0-ae57-7874ee7fa522,0c35c1a4-3027-411d-ac90-0e8ad64ba272}');
```