Subject: Create index on user defined type

From: aditya desai <[email-masked]>
Date: Tue, 19 Apr 2022 21:57:18 +0530

Hi,
Is there any way to create index on user defined type in Postgresql? Need
to create index on bug_status in bug table.

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);

Regards,
Aditya.

---

From: Christophe Pettus <[email-masked]>
Date: Tue, 19 Apr 2022 09:29:37 -0700

> On Apr 19, 2022, at 09:27, aditya desai <[email-masked]> wrote:
> 
> Hi,
> Is there any way to create index on user defined type in Postgresql? Need to create index on bug_status in bug table.
> 

It works right out of the box:

xof=# CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
CREATE TYPE
xof=# CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);
CREATE TABLE
xof=# create index on bug(status);
CREATE INDEX
xof=#

---

From: aditya desai <[email-masked]>
Date: Tue, 19 Apr 2022 22:33:17 +0530

Thanks! Apologies! Looks like I was having issues with NULL values and
UNIQUE index on user defined column.

On Tue, Apr 19, 2022 at 10:00 PM Christophe Pettus <[email-masked]> wrote:

> > Is there any way to create index on user defined type in Postgresql?
> Need to create index on bug_status in bug table.
> >

---

From: Rob Sargent <[email-masked]>
Date: Tue, 19 Apr 2022 12:18:32 -0600

On 4/19/22 11:03, aditya desai wrote:
>     > Is there any way to create index on user defined type in
>     Postgresql? Need to create index on bug_status in bug table.
>     >
I would have to wonder at the effectiveness an index on such a small 
number of possible values.

---

From: Samed YILDIRIM <[email-masked]>
Date: Sun, 24 Apr 2022 13:43:23 +0300

Hi,

It is really good point. I haven't had a chance to test. But, deduplication feature of B-tree indexes introduced with PostgreSQL 13 can make it very effective. Otherwise, it is worth to try using GIN index on such columns with small number of distinct values.

Best regards.

Samed YILDIRIM

19.04.2022, 21:18, "Rob Sargent" <[email-masked]>:

Thanks! Apologies! Looks like I was having issues with NULL values and UNIQUE index on user defined column.

I would have to wonder at the effectiveness an index on such a small number of possible values.