CODEWITHSUNDEEP
postgresql
Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2725

Create index on composite type in Postgres

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

Upvotes: 3

Views: 603

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

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));

Upvotes: 3

Related Questions