Postgresql – Create index on multicolumn type

postgresql

Using Postgres 9.6

I am trying to create an index on a type adt_flds with a field also named adt_flds

 create type adt_flds as
(
      created_at timestamp
    , updated_at timestamp
    , created_by text
    , updated_by text
    , created_by_client_addr inet
    , updated_by_client_addr inet
    , created_by_client_cmptr text
    , updated_by_client_cmptr text
);

My attempt is as follows for

schema = document

table = training_complete_history

field = adt_flds

field column = created_at

SQL 

create index training_complete_history_adt_flds_index
on document.training_complete_history ((adt_flds).created_at);

Unfortunately, my attempt yields the following error.

[42601] ERROR: syntax error at or near ")" Position: 114.

How would I properly index created_at inside adt_flds?

Best Answer

You need an extra pair of parentheses around the indexed term if it is not a column or a function call.

In the words of the documentation:

The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.

CREATE INDEX training_complete_history_adt_flds_index
ON document.training_complete_history (((adt_flds).created_at));

I think it is a bad idea to use a composite type if you want to use parts of it in a WHERE or join condition. It violates the first normal form.

Related Question