PostgreSQL Indexes on Expressions

Pyrseas had its first release a little over a year ago and we now have our first backward compatibility issue. The first release included basic support for traditional indexes, i.e., one or more key columns. For example, given a table test1 with columns col1, col2 and col3, and an index on the last two, dbtoyaml would show (some details omitted):

table test1:
  columns:
  - col1
  - col2
  - col3
  indexes:
    test1_idx:
      columns:
      - col2
      - col3

One of the first issues reported the lack of support for “functional” indexes. I added that but unfortunately, didn’t realize that one can have more than one function or expression and even mix regular columns with expressions. Thus the support was limited to a single expression. Given the first example in the “Indexes on Expressions” documentation, dbtoyaml would show:

table test1:
  columns:
  ...
  indexes:
    test1_lower_col1_idx:
      expression:
        lower(col1)

The original issue was recently re-opened (thanks, Roger) to point out the deficiencies. A fix has been pushed. Thus in the next release, dbtoyaml will support indexes with multiple expressions and even combinations of functions and regular columns. Here is a weird example using the first table. Given CREATE INDEX test1_idx ON test1 (btrim(col3, 'x') NULLS FIRST, col1, lower(col2) DESC), dbtoyaml now outputs:

table test1:
  indexes:
    test1_idx:
      access_method: btree
      keys:
      - btrim(c3, 'x'::text):
          nulls: first
          type: expression
      - col1
      - lower(col2):
          order: desc
          type: expression

So instead of ‘columns’ (or ‘expression’), dbtoyaml outputs ‘keys’. Any key that is an expression is marked with the ‘type’ qualifier.To allow for backward compatibility, yamltodb will continue to accept ‘columns’, so existing YAML specs  with traditional indexes won’t need to be changed. However, if you have an index using an expression, you’ll have to edit as seen above.

Do you have an unusual index?  Try dbtoyaml (from GitHub) on it and let us know if it works (or not).