I wish to examine a catalog (along with other objects) from psql.

I produced the index with:

CREATE INDEX my_index on "my_table" (iso_country_code, type_name, UPPER(name) varchar_pattern_ops);

In psql I made use of the describe command:

db=> \d+ my_index
Index "public.my_index"
      Column      |          Type          | Storage  | Description 
------------------+------------------------+----------+-------------
 iso_country_code | character varying(3)   | extended | 
 type_name        | character varying(300) | extended | 
 pg_expression_3  | text                   | extended | 
btree, for table "public.my_table"

The expression within the last area from the index states only pg_expression_3. How come the expression not display in the psql output?

I'm able to retrieve the table schema such as the CREATE INDEX claims using pg_dump:

pg_dump --table my_table --schema-only my_db

It's clearly pre-9. behavior, have a look into [cde]:

  • Show definitions of index posts in E.5. Release 9.0 (Khee Face)

    The meaning is helpful for expression indexes.

For previous versions you are able to still fetch expression by hand crossing system catalogs and taking advantage of [cde] function, e.g.:

\d index_name