Manual schema specification for tables is not compatible with index creation statements

[terpsquared]

A method for specifying postgres schemas to be used within Django is specified ​here.

From this, the syntax for specifying a schema-located table when declaring a Django model is:

    class Meta:
        db_table = '"myschema"."mytable"'

This is compatible with the creation of tables; however, when generating indices, improper names are generated.

The SQL it generates is in the following format:

CREATE INDEX ""myschema"."mytable"_my_column_id" ON "myschema"."mytable" ("my_column_id");

The quoting mechanism is not currently fully compatible with this syntax; while it could be considered non-standard Django, it would seem that correcting this problem would not be overly difficult, and should not interfere with typical functionality.

The fix in django/db/backends/postgresql/creation.py is as follows:

            def get_index_sql(index_name, opclass=''):
                return (style.SQL_KEYWORD('CREATE INDEX') + ' ' +
                        style.SQL_TABLE(qn(index_name)) + ' ' +
                        style.SQL_KEYWORD('ON') + ' ' +
                        style.SQL_TABLE(qn(db_table)) + ' ' +
                        "(%s%s)" % (style.SQL_FIELD(qn(f.column)), opclass) +
                        "%s;" % tablespace_sql)

becomes

            def get_index_sql(index_name, opclass=''):
                return (style.SQL_KEYWORD('CREATE INDEX') + ' ' +
                        style.SQL_TABLE(qn(index_name.replace('"', '').replace('.', '_'))) + ' ' +
                        style.SQL_KEYWORD('ON') + ' ' +
                        style.SQL_TABLE(qn(db_table)) + ' ' +
                        "(%s%s)" % (style.SQL_FIELD(qn(f.column)), opclass) +
                        "%s;" % tablespace_sql)

I have attached a patch for this change.

[terpsquared]

patch

[Russell Keith-Magee]

I'm going to close this as a dupe of #6148. Django doesn't currently have *any* support for schemas, manually specified or otherwise. If we're going to add schema support, we're not just going to do it for manually specified table names. The fact that the technique from the mailing list works at all is accident, not intention.