I am running Postgres 12 I have an audit field composite type I made for keeping my tables from being too fat.
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 ); create table if not exists excel.equip_type ( equip_type_id serial not null constraint equip_type_pk primary key, descrip text, adt_flds adt_flds ); --Creates a syntax error create index equip_type_adt_flds_index on excel.equip_type (adt_flds.created_at);
I am to the point where I would like to index the created_at field so when I filter on created_at in my WHERE statements, my queries can index scan and not heap scan. My issue is that I end up with a syntax error on my create index statement. How would I fix my syntax up so I can have an index on a just 1 element of my
 ERROR: syntax error at or near ")" Position: 85
The PostgreSQL documentation is pretty clear about the syntax for indexes that are created on an expression rather than a table column:
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.
You need additional parentheses for disambiguation.
CREATE INDEX equip_type_adt_flds_index ON excel.equip_type (((adt_flds).created_at));