Thread: default operator class (PostgreSQL's error?)

I create a scalar type name_t.  Then I define the operator class for
it:

create operator class name_ops   default for type name_t   using btree as       operator 1 <,       operator 2 <=,
operator 3 =,       operator 4 >=,       operator 5 >,       function 1 name_cmp (name_t,name_t);
 

And it is DEFAULT for the type.  Then I create some table:

create table name_l (   name name_t       unique       not null,   key smallint       primary key,   sex sex
);

And the UNIQUE constraint leads to the next error:

ERROR:  data type name_t has no default operator class for access method "btree"       You must specify an operator
classfor the index or define a       default operator class for the data type
 

(There is no (of course?) such an error if I not use the UNIQUE
constraint).  So, does this mean that it's an error in PostreSQL or it
is my fail thru some way?

Thanks in advance.

Denis Zaitsev <zzz@cd-club.ru> writes:
> ERROR:  data type name_t has no default operator class for access method "btree"

Hm, that looks like it should work.  You sure you marked the opclass
default?  (Check its row in pg_opclass to see.)

Another possibility is that the opclass is in a schema that is not in
your search path --- I can't recall right now whether being in the
search path affects lookup of a default opclass.
        regards, tom lane

On Sun, Mar 02, 2003 at 01:01:40PM -0500, Tom Lane wrote:
> Denis Zaitsev <zzz@cd-club.ru> writes:
> > ERROR:  data type name_t has no default operator class for access method "btree"
> 
> Hm, that looks like it should work.  You sure you marked the opclass
> default?  (Check its row in pg_opclass to see.)

Yes, it is default.

> Another possibility is that the opclass is in a schema that is not in
> your search path --- I can't recall right now whether being in the
> search path affects lookup of a default opclass.

It is in a schema, and the schema is definitely in the search_path -
it's pointed first there.  More over, all the games described are
being played in that schema. 

I already had a though about schemas and tried to explicitly qualify
the type (name_t) of the attribute - nothing had changed...

Denis Zaitsev <zzz@cd-club.ru> writes:
> On Sun, Mar 02, 2003 at 01:01:40PM -0500, Tom Lane wrote:
>> Another possibility is that the opclass is in a schema that is not in
>> your search path --- I can't recall right now whether being in the
>> search path affects lookup of a default opclass.

> It is in a schema, and the schema is definitely in the search_path -
> it's pointed first there.  More over, all the games described are
> being played in that schema. 

Well, it works for me: for example, after building and installing the
contrib/isbn_issn module, I can do

opc=# create table isbn_t (
opc(#     isbn  isbn unique not null
opc(# );
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'isbn_t_isbn_key' for    table 'isbn_t'
CREATE TABLE

I looked at the code and saw that indeed a default opclass must be
visible in your search path to be found, so I suggest taking another
look at that aspect.
        regards, tom lane

I've found a reason!  It's some namespace problem - there are other
tho name_ops operator classes exist.  My becomes third.  All are the
default for (their) type.  And somewhere there is the issue.  Renaming
my operator class into, say, name_t_ops resolves the problem.

Thanks for the info.