Обсуждение: create index on a field of udt

On 6/28/2015 10:08 PM, Shujie Shang wrote:

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

I want to create index on info.id.

you can't create an index on a type, just on a table.

    create table info (id serial primary key, name text);

or

    create table info (id serial, name text);
    alter table info add primary key(id);

or more generically,

    create index on some_table ( some_field[,...] ) ;

(a primary key is a unique not null constraint, this implies an index in postgresql)

-- 
john r pierce, recycling bits in santa cruz

Hello

 

I am not sure it is that simple. Probably you need to create operator classes to be used for indexing.

 

http://www.postgresql.org/docs/9.4/static/xtypes.html

 

You are probably better off using the basic data type in your table and using a composite index.

 

Bye

Charles

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Montag, 29. Juni 2015 07:51
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create index on a field of udt

 

On 6/28/2015 10:31 PM, Shujie Shang 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)

    create table test of info primary key(id);

or, if you want to use your type plus other stuff in the table, I believe its something like...

    create table test (i info, stuff...) primary key (i.id)
or
    create index test(i.id);


watch out for ambiguity if the type names match the table or field name.  see http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7836

-- 

john r pierce, recycling bits in santa cruz