Content-Type: multipart/alternative;
 boundary="------------aZx8bDpG6NGTpj3eUVyK8FPb"
Message-ID: <54858e28-402d-92fc-3c65-c9f82763112a@gmail.com>
Date: Tue, 19 Apr 2022 12:18:32 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: Create index on user defined type
Content-Language: en-CA
To: pgsql-sql@lists.postgresql.org
References: 
 <CAN0SRDHgW+5-yGdLE-y6eCpWdffV7iexabGL1PmZOb7BU=QeWg@mail.gmail.com>
 <4D6ADD6D-0E11-4E6C-BF6D-AFB57FFF1B67@thebuild.com>
 <CAN0SRDHH=10fPDh2tTY0cjLdTzESeaoV1YRPgk=mvxhcNqM_=w@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CAN0SRDHH=10fPDh2tTY0cjLdTzESeaoV1YRPgk=mvxhcNqM_=w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/54858e28-402d-92fc-3c65-c9f82763112a%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------aZx8bDpG6NGTpj3eUVyK8FPb
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 4/19/22 11:03, aditya desai wrote:
> 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=#
>
I would have to wonder at the effectiveness an index on such a small 
number of possible values.
--------------aZx8bDpG6NGTpj3eUVyK8FPb
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 4/19/22 11:03, aditya desai wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAN0SRDHH=10fPDh2tTY0cjLdTzESeaoV1YRPgk=mvxhcNqM_=w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Thanks! Apologies! Looks like I was having issues
        with NULL values and UNIQUE index on user defined column.</div>
      <br>
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Tue, Apr 19, 2022 at 10:00
          PM Christophe Pettus &lt;<a href="mailto:xof@thebuild.com"
            moz-do-not-send="true" class="moz-txt-link-freetext">xof@thebuild.com</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote" style="margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
          &gt; On Apr 19, 2022, at 09:27, aditya desai &lt;<a
            href="mailto:admad123@gmail.com" target="_blank"
            moz-do-not-send="true" class="moz-txt-link-freetext">admad123@gmail.com</a>&gt;
          wrote:<br>
          &gt; <br>
          &gt; Hi,<br>
          &gt; Is there any way to create index on user defined type in
          Postgresql? Need to create index on bug_status in bug table.<br>
          &gt; <br>
          &gt; CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');<br>
          &gt; <br>
          &gt; CREATE TABLE bug (<br>
          &gt;     id serial,<br>
          &gt;     description text,<br>
          &gt;     status bug_status<br>
          &gt; );<br>
          <br>
          It works right out of the box:<br>
          <br>
          xof=# CREATE TYPE bug_status AS ENUM ('new', 'open',
          'closed');<br>
          CREATE TYPE<br>
          xof=# CREATE TABLE bug (<br>
              id serial,<br>
              description text,<br>
              status bug_status<br>
          );<br>
          CREATE TABLE<br>
          xof=# create index on bug(status);<br>
          CREATE INDEX<br>
          xof=# <br>
          <br>
        </blockquote>
      </div>
    </blockquote>
    <font face="Courier New, Courier, monospace">I would have to wonder
      at the effectiveness an index on such a small number of possible
      values. </font><br>
  </body>
</html>

--------------aZx8bDpG6NGTpj3eUVyK8FPb--