Thread: Create index on user defined type

> On Apr 19, 2022, at 09:27, aditya desai <admad123@gmail.com> 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.
>
> CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
>
> CREATE TABLE bug (
>     id serial,
>     description text,
>     status bug_status
> );

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=#

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 <xof@thebuild.com> wrote:

> On Apr 19, 2022, at 09:27, aditya desai <admad123@gmail.com> 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.
>
> CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
>
> CREATE TABLE bug (
>     id serial,
>     description text,
>     status bug_status
> );

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=#