public inbox for [email protected]
help / color / mirror / Atom feedCreate index on user defined type
5+ messages / 4 participants
[nested] [flat]
* Create index on user defined type
@ 2022-04-19 16:27 aditya desai <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: aditya desai @ 2022-04-19 16:27 UTC (permalink / raw)
To: pgsql-sql <[email protected]>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create index on user defined type
@ 2022-04-19 16:29 Christophe Pettus <[email protected]>
parent: aditya desai <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Christophe Pettus @ 2022-04-19 16:29 UTC (permalink / raw)
To: aditya desai <[email protected]>; +Cc: pgsql-sql <[email protected]>
> On Apr 19, 2022, at 09:27, aditya desai <[email protected]> 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=#
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create index on user defined type
@ 2022-04-19 17:03 aditya desai <[email protected]>
parent: Christophe Pettus <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: aditya desai @ 2022-04-19 17:03 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: pgsql-sql <[email protected]>
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 protected]> wrote:
>
> > On Apr 19, 2022, at 09:27, aditya desai <[email protected]> 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=#
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create index on user defined type
@ 2022-04-19 18:18 Rob Sargent <[email protected]>
parent: aditya desai <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Rob Sargent @ 2022-04-19 18:18 UTC (permalink / raw)
To: [email protected]
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 <[email protected]>
> wrote:
>
>
> > On Apr 19, 2022, at 09:27, aditya desai <[email protected]> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create index on user defined type
@ 2022-04-24 10:43 Samed YILDIRIM <[email protected]>
parent: Rob Sargent <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Samed YILDIRIM @ 2022-04-24 10:43 UTC (permalink / raw)
To: Rob Sargent <[email protected]>; [email protected] <[email protected]>
<div><div>Hi,</div><div> </div><div>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.</div></div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>19.04.2022, 21:18, "Rob Sargent" <[email protected]>:</div><blockquote><div>On 4/19/22 11:03, aditya desai wrote:</div><blockquote><div>Thanks! Apologies! Looks like I was having issues with NULL values and UNIQUE index on user defined column.</div> <div><div>On Tue, Apr 19, 2022 at 10:00 PM Christophe Pettus <<a href="mailto:[email protected]" rel="noopener noreferrer">[email protected]</a>> wrote:</div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><br />> On Apr 19, 2022, at 09:27, aditya desai <<a href="mailto:[email protected]" rel="noopener noreferrer" target="_blank">[email protected]</a>> wrote:<br />><br />> Hi,<br />> Is there any way to create index on user defined type in Postgresql? Need to create index on bug_status in bug table.<br />><br />> CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');<br />><br />> CREATE TABLE bug (<br />> id serial,<br />> description text,<br />> status bug_status<br />> );<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 /> </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></blockquote>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-04-24 10:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-19 16:27 Create index on user defined type aditya desai <[email protected]>
2022-04-19 16:29 ` Christophe Pettus <[email protected]>
2022-04-19 17:03 ` aditya desai <[email protected]>
2022-04-19 18:18 ` Rob Sargent <[email protected]>
2022-04-24 10:43 ` Samed YILDIRIM <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox