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 adt_flds type?
[42601] 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.
Try
CREATE INDEX equip_type_adt_flds_index
ON excel.equip_type (((adt_flds).created_at));