Combination of Array and string with explicit collation (PostgreSQL)

[Rudolph Froger]

This field:

from sqlalchemy.dialects.postgresql import ARRAY
...

keywords = Column(ARRAY(Unicode(100, collation='en_US'), dimensions=1))

Gives an error when trying to create all tables:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "["
LINE 7:  keywords VARCHAR(100) COLLATE "en_US"[], 

This is the generated SQL:

CREATE TABLE example_table (keywords VARCHAR(100) COLLATE "en_US"[]);

The correct SQL should be:

CREATE TABLE example_table (keywords VARCHAR(100)[] COLLATE "en_US");

[Mike Bayer]

https://gerrit.sqlalchemy.org/425

I don't have much of a workaround for you against current release other than make a subclass of ARRAY and then build out a custom @compiles : http://docs.sqlalchemy.org/en/latest/core/custom_types.html#overriding-type-compilation

[Rudolph Froger]

Wow, that's fast. Thanks a lot!

[Mike Bayer]

Render ARRAY index embedded between type and COLLATE

Fixed bug where using :class:.ARRAY with a string type that features a collation would fail to produce the correct syntax within CREATE TABLE.

The "COLLATE" must appear to the right of the array dimensions, so we are using regexp substitution to insert the brackets in the appropriate place. A more heavyweight solution would be that datatypes know how to split up their base type vs. modifiers, but as this is so specific to Postgresql ARRAY it's better to handle these cases more locally.

Change-Id: I394c3c673eb60689e51b5301e51651972cfdb4c0 Fixes: #4006

→ <<cset ec422fb70e00>>

[Mike Bayer]

Render ARRAY index embedded between type and COLLATE

Fixed bug where using :class:.ARRAY with a string type that features a collation would fail to produce the correct syntax within CREATE TABLE.

The "COLLATE" must appear to the right of the array dimensions, so we are using regexp substitution to insert the brackets in the appropriate place. A more heavyweight solution would be that datatypes know how to split up their base type vs. modifiers, but as this is so specific to Postgresql ARRAY it's better to handle these cases more locally.

Change-Id: I394c3c673eb60689e51b5301e51651972cfdb4c0 Fixes: #4006 (cherry picked from commit ec422fb70e0044ed42dcfda5fb1a7a65db322cf1)

→ <<cset 755334ec4e54>>