Commits

Mike Bayer committed 6ca35b1

- Added new "postgresql_ops" argument to
Index, allows specification of PostgreSQL
operator classes for indexed columns.
[ticket:2198] Courtesy Filip Zyzniewski.

  • Participants
  • Parent commits 6967c6e

Comments (0)

Files changed (5)

     "retryable" condition.  Only Oracle ORA-01033
     implemented for now.  [ticket:2201]
 
+- postgresql
+  - Added new "postgresql_ops" argument to 
+    Index, allows specification of PostgreSQL
+    operator classes for indexed columns.
+    [ticket:2198]  Courtesy Filip Zyzniewski.
+
 - mssql
   - Adjusted the pyodbc dialect such that bound
     values are passed as bytes and not unicode

doc/build/core/schema.rst

 .. autoclass:: UniqueConstraint
     :show-inheritance:
 
+.. _schema_indexes:
 
 Indexes
 -------

lib/sqlalchemy/dialects/postgresql/base.py

         where(table.c.name=='foo')
     print result.fetchall()
 
-Indexes
--------
 
-PostgreSQL supports partial indexes. To create them pass a postgresql_where
-option to the Index constructor::
+.. _postgresql_indexes:
+
+Postgresql-Specific Index Options
+---------------------------------
+
+Several extensions to the :class:`.Index` construct are available, specific
+to the PostgreSQL dialect.
+
+Partial Indexes
+^^^^^^^^^^^^^^^^
+
+Partial indexes add criterion to the index definition so that the index is 
+applied to a subset of rows.   These can be specified on :class:`.Index`
+using the ``postgresql_where`` keyword argument::
 
   Index('my_index', my_table.c.id, postgresql_where=tbl.c.value > 10)
 
+Operator Classes
+^^^^^^^^^^^^^^^^^
+
+PostgreSQL allows the specification of an *operator class* for each column of
+an index (see http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html).
+The :class:`.Index` construct allows these to be specified via the ``postgresql_ops``
+keyword argument (new as of SQLAlchemy 0.7.2)::
+
+    Index('my_index', my_table.c.id, my_table.c.data, 
+                            postgresql_ops={
+                                'data': 'text_pattern_ops', 
+                                'id': 'int4_ops'
+                            }) 
+
+Note that the keys in the ``postgresql_ops`` dictionary are the "key" name of
+the :class:`.Column`, i.e. the name used to access it from the ``.c`` collection
+of :class:`.Table`, which can be configured to be different than the actual
+name of the column as expressed in the database.
+
 """
 
 import re
         text = "CREATE "
         if index.unique:
             text += "UNIQUE "
+        ops = index.kwargs.get('postgresql_ops', {})
         text += "INDEX %s ON %s (%s)" \
-                % (preparer.quote(
-                    self._index_identifier(index.name), index.quote),
-                   preparer.format_table(index.table),
-                   ', '.join([preparer.format_column(c) 
-                                for c in index.columns]))
+                % (
+                    preparer.quote(
+                        self._index_identifier(index.name), index.quote),
+                    preparer.format_table(index.table),
+                    ', '.join([
+                        preparer.format_column(c) + 
+                        (c.key in ops and (' ' + ops[c.key]) or '')
+                        for c in index.columns])
+                    )
 
         if "postgres_where" in index.kwargs:
             whereclause = index.kwargs['postgres_where']

lib/sqlalchemy/schema.py

 
     Defines a composite (one or more column) INDEX. For a no-frills, single
     column index, adding ``index=True`` to the ``Column`` definition is
-    a shorthand equivalent for an unnamed, single column Index.
+    a shorthand equivalent for an unnamed, single column :class:`.Index`.
+    
+    See also:
+    
+    :ref:`schema_indexes` - General information on :class:`.Index`.
+
+    :ref:`postgresql_indexes` - PostgreSQL-specific options available for the :class:`.Index` construct.
     """
 
     __visit_name__ = 'index'

test/dialect/test_postgresql.py

                             "WHERE data > 'a' AND data < 'b''s'",
                             dialect=postgresql.dialect())
 
+    def test_create_index_with_ops(self):
+        m = MetaData()
+        tbl = Table('testtbl', m,
+                    Column('data', String), 
+                    Column('data2', key='d2', Integer))
+
+        idx = Index('test_idx1', tbl.c.data,
+                    postgresql_ops={'data': 'text_pattern_ops'})
+
+        idx2 = Index('test_idx2', tbl.c.data, tbl.c.d2,
+                    postgresql_ops={'data': 'text_pattern_ops',
+                                    'd2': 'int4_ops'})
+
+        self.assert_compile(schema.CreateIndex(idx),
+                            'CREATE INDEX test_idx1 ON testtbl '
+                            '(data text_pattern_ops)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx2),
+                            'CREATE INDEX test_idx2 ON testtbl '
+                            '(data text_pattern_ops, data2 int4_ops)',
+                            dialect=postgresql.dialect())
+
     @testing.uses_deprecated(r".*'postgres_where' argument has been "
                              "renamed.*")
     def test_old_create_partial_index(self):