Create index on composite type in Postgres

Question

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

Did you mean equip_type instead of equip_usage? And would you please show us your syntax error? — Schwern
– Schwern, Commented Jun 11, 2020 at 16:45

Laurenz Albe · Accepted Answer · 2020-06-11 20:46:13Z

3

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

answered Jun 11, 2020 at 20:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create index on composite type in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related