Re: [JDBC] Selecting Varchar range (through JDBC). - Mailing list pgsql-general

Really? The collating elements are locale-specific?!? They are actually
hard-coded somewhere??

If this is so, and this is what is meant by the definition of a collating
element, then I am going about things the entirely wrong way when trying to
select a varchar range. In which case I would be tempted to do something
like this instead:

testdb=# select 'test' ~* '^[[.a.]-[.z.]][[.a.]-[.a.]]';
    [same as]
testdb=# select 'test' ~* '^[a-z][a-a]';

Of course, this won't match aa-za, because it won't match ab, ac, ..., az,
bb, bc, ..., etc.

So, does anybody have any advice on how to select a range?

Stephan, thanks for clearing me up on the collating elements. Perhaps it
should be in the docs that they are locale-specific ... so that simpletons
like me won't be misled into thinking that they can just make up collating
elements as they seem fit.

Sincerely,

    Daryl.



> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Tuesday, September 10, 2002 11:20 AM
> To: Daryl Beattie
> Cc: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] [GENERAL] Selecting Varchar range (through JDBC).
>
>
> Stephan Szabo sszabo@bigpanda.com
>
> On Tue, 10 Sep 2002, Daryl Beattie wrote:
>
> > Dear PostgreSQL people,
> >
> >     A little while ago I posted a message about selecting a varchar
> > range through JDBC. Thomas was nice enough to give me some
> pointers, and I
> > went back to the database and tested some things out. The
> clincher came when
> > I realized that one of my JUnit tests was actually passing
> using the varchar
> > range; some of the regular expressions worked, while others did not.
> >     What I found is that using the collating elements in regex
> > comparisons does not work if the collating arguments are
> more than one
> > character in length. According to
> >
http://www.postgresql.org/idocs/index.php?functions-matching.html#FUNCTIONS-
> REGEXP, regular expressions should work with multi-character collating
> arguments. I did a bunch of tests at the PostgreSQL command-line to verify
> this finding. Here are some highlights:
>
> testdb=# select 'test' ~* '^[[.a.]-[.z.]]';
>  ?column?
> ----------
>  t
> (1 row)
>
> testdb=# select 'test' ~* '^[[.aa.]-[.za.]]';
> ERROR:  Invalid regular expression: invalid collating element
> testdb=# select 'test' ~* '^[[.a.]-[.za.]]';
> ERROR:  Invalid regular expression: invalid collating element
> testdb=# select 'test' ~* '^[[.aa.]-[.z.]]';

Are you really in a locale where aa or za are collating elements?

> ERROR:  Invalid regular expression: invalid collating element
> testdb=# select 'chchcc' ~ '[[.ch.]]*c';
> ERROR:  Invalid regular expression: invalid collating element
>
>
>     That last test was taken straight from the PostgreSQL interactive
> documentation at the link that I provided above. The documentation states
> that it should match.

Note the caveat in the paragraph above.  If the collating sequence
includes a ch collating element...
It means if you're in a locale where ch collates as a single element you
can use .ch. to refer to that element.