Re: Request: set opclass for generated unique and primary

On Wed, 22 Feb 2006, Pavel Stehule wrote:

>
>
> >Why would you need it?
> I can't to use unique index for like_op without setting opclass, because I
> have to use czech locale. I can create second index, but then I have two
> equal indexes. Example:
>
> number |  description
> 000102  blabla bla
> 000103  bbbb fooo
>
> number: varchar primary key.
>
> Sometimes I need search all values with one prefix ~ like '0001%'. That's
> all.
>
> >
> > >   USING INDEX [TABLESPACE ..] [OPCLASS ..]
> >
> >This is unworkable --- consider a table with more than one unique
> >constraint and/or multiple-column constraints.
> >
> I forgot (full syntax is):
> CREATE TABLE ....
>    number varchar PRIMARY KEY USING OPCLAS varchar_pattern_ops,

My problem with this is that the above isn't a primary key by a strict
definition and may not have the normal semantics for primary keys.  For
example, is said key a valid target for foreign keys?  I don't think it
necessarily is, unless we can always guarantee that it's unique for normal
equality as well or we allow you to specify a different equality operator
for that case which matches the one in the opclass.

> >I seem to recall someone proposing extending the syntax of the UNIQUE
> >constraints themselves, but there really isn't enough use-case to
> >justify it AFAICS.  Especially not when you can always use CREATE UNIQUE
> >INDEX.
>
> I can always use second unique index. But it's redundant. This problem is
> related to using nonC locale.

Why do you need both the unique index with varchar_pattern_ops and one
with the default ops?