0

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?

2
1

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.

1
  • I don't disagree. However the creation of this was not in my control. There was a complaint of too many audit fields at our organization. This was the solution derived at the time to resolve the complaints. – Daniel L. VanDenBosch Mar 9 '20 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.