Skip site navigation (1) Skip section navigation (2)

Re: Request: set opclass for generated unique and primary

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)hotmail(dot)com>
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us, pvasickova(at)hotmail(dot)com,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Request: set opclass for generated unique and primary
Date: 2006-02-22 22:09:05
Message-ID: 20060222135503.B77939@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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?

In response to

Responses

pgsql-hackers by date

Next:From: Mark KirkwoodDate: 2006-02-22 22:11:47
Subject: Re: pg_config, pg_service.conf, postgresql.conf ....
Previous:From: Shaun ThomasDate: 2006-02-22 22:02:31
Subject: Attempting upgrade path; is this possible?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group