Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ngrGK-0001yI-Vs for pgsql-sql@arkaria.postgresql.org; Tue, 19 Apr 2022 17:03:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ngrGJ-0008PX-9D for pgsql-sql@arkaria.postgresql.org; Tue, 19 Apr 2022 17:03:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ngrGI-0008PD-VF for pgsql-sql@lists.postgresql.org; Tue, 19 Apr 2022 17:03:35 +0000 Received: from mail-lj1-x232.google.com ([2a00:1450:4864:20::232]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ngrGE-0008Sn-Jr for pgsql-sql@lists.postgresql.org; Tue, 19 Apr 2022 17:03:34 +0000 Received: by mail-lj1-x232.google.com with SMTP id q14so21298800ljc.12 for ; Tue, 19 Apr 2022 10:03:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=x0JASdYdSMi3CQJHubAcMTBBlouB/66ts5iQMiYu2II=; b=TFNtadm4aOojRaR8yS0e8TP7Izk7P8/eoiaClX2BzPVToEOgCPmyxhHG8psqjOW0cE YqYXuNXwIgxFV8Rs02KP5iTAxBX8NZkMFnGi1UzAk8+Ar4XzCJBj5ge7Hy95uYm7M/Rl uVAjmZgylV7vFrF74UGuOdN/Iu6r2sYv5PgfiN0SCLxku33EfJjKQ8w/6wjH9vhmhbUw HKgH2am0PDG6J+cOtZOELoh0MvfvBt3YtQi7NRAk3yBwGD08rrgkYjwXrbWEVolJpCLY 5qb4+T0RWVFJOkRNmTRcZBlZXp2sBU9x80/MeU7SymXk6CCeHnBgLcj2JgvMZe4DgjvR K7zg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=x0JASdYdSMi3CQJHubAcMTBBlouB/66ts5iQMiYu2II=; b=5yNLfBsSjd+kLuuaQQPNgsN1oU2QqUR3cqIsRWwX0Eqr4MQTsnsbeI77HMk5Yj5CIU 6PDqyNTlPYeAthTpGe2gOBlu0G3Z+Bob2z4wVaVxSvNCpFHVbFD3SRMNyIaUpcxnhKeP U2m0RoJ/FVH6hDpJF3UWtuuU691qYZP3ujeSyLw4WDGdNAe0SwqwuyO/EK4pKvTgVDxN EDIcJ/eGNLAeXSppg6DrU6RcwS9TRi1piW9QXWOIoMmUt3jWL8vdq3GTYB33NZB9lzhA P45a5mCaUckpvZaNdtTjllzMcGrYoYA3KcNet240vBE8SxSO9auyqxfI+wXugBoTqZu5 FQQg== X-Gm-Message-State: AOAM532oAJAWKGhJW/gumZ7w8P6D3wJL6lqgNmobXokgZX+uhpb+ZP+e Yc/oRys6wFokm8YAjv30e92N61S3AQbuaOKnezMF2RpB X-Google-Smtp-Source: ABdhPJzhBbfID0aHpKd/ocmxGW6aKotowMCfVIurih3wnpqPbOyhSYJNjsWTpXj9X5vxfXvbF2R65mLsDi/h7PxiwSo= X-Received: by 2002:a2e:9284:0:b0:24d:a7a2:5402 with SMTP id d4-20020a2e9284000000b0024da7a25402mr10650196ljh.65.1650387808790; Tue, 19 Apr 2022 10:03:28 -0700 (PDT) MIME-Version: 1.0 References: <4D6ADD6D-0E11-4E6C-BF6D-AFB57FFF1B67@thebuild.com> In-Reply-To: <4D6ADD6D-0E11-4E6C-BF6D-AFB57FFF1B67@thebuild.com> From: aditya desai Date: Tue, 19 Apr 2022 22:33:17 +0530 Message-ID: Subject: Re: Create index on user defined type To: Christophe Pettus Cc: pgsql-sql Content-Type: multipart/alternative; boundary="00000000000015ae1005dd04dbf3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000015ae1005dd04dbf3 Content-Type: text/plain; charset="UTF-8" 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 wrote: > > > On Apr 19, 2022, at 09:27, aditya desai 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=# > > --00000000000015ae1005dd04dbf3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks! Apologies! Looks like I was having issues with NUL= L values and UNIQUE index on user defined column.

On Tue, Apr 19, 2022 at 10= :00 PM Christophe Pettus <xof@thebui= ld.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? N= eed to create index on bug_status in bug table.
>
> CREATE TYPE bug_status AS ENUM ('new', 'open', 'cl= osed');
>
> CREATE TABLE bug (
>=C2=A0 =C2=A0 =C2=A0id serial,
>=C2=A0 =C2=A0 =C2=A0description text,
>=C2=A0 =C2=A0 =C2=A0status bug_status
> );

It works right out of the box:

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

--00000000000015ae1005dd04dbf3--