Re: create index on a field of udt - Mailing list pgsql-general

From Charles Clavadetscher
Subject Re: create index on a field of udt
Date
Msg-id 005901d0b23e$f6becbc0$e43c6340$@swisspug.org
Whole thread Raw
In response to Re: create index on a field of udt  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general

+1

 

create index on test (((i).id));

ANALYZE

explain select * from test where (i).id = 8909;

                               QUERY PLAN

-------------------------------------------------------------------------

Index Scan using test_id_idx on test  (cost=0.43..8.45 rows=1 width=34)

   Index Cond: ((i).id = 8909)

(2 rows)

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Janes
Sent: Montag, 29. Juni 2015 08:42
To: Shujie Shang
Cc: John R Pierce; PostgreSQL mailing lists
Subject: Re: [GENERAL] create index on a field of udt

 

On Sun, Jun 28, 2015 at 10:31 PM, Shujie Shang <sshang@pivotal.io> wrote:

Oh, I didn't explain my question well, actually I want to create an index on an udt in a table.

 

e.g.

create type info as (id int, name text);

creat table test (i info);

I want to run:

create index myindex on test (i.id)

 

 

 

It is a matter of finding the correct level of magic parentheses.

 

create index on test (((i).id));

 

The outer layer are always needed for creating indexes.  The middle layer are needed because you are indexing an expression, not a column.  And the inner layer is needed because, well, that is just how udt works.

 

Cheers,

 

Jeff

pgsql-general by date:

Previous
From: Andy Erskine
Date:
Subject: Re: Turn off streaming replication - leaving Master running
Next
From: Xavier 12
Date:
Subject: Re: pg_xlog on a hot_stanby slave