Non-C locale and LIKE

However, I am wondering if we should create a character
lookup during initdb that has the characters ordered so we can do:

col LIKE 'ha%' AND col >= "ha" and col <= "hb"

Could we do this easily for single-character encodings? We
could have:

A 1
B 2
C 3

and a non-C locale could be:

A 1
A` 2
B 3

We can't handle multi-byte encodings because the number of
combinations is too large or not known.

Also, we mention you should use the "C" locale to use normal
indexes for LIKE but isn't it more correct to say the
encoding has to be SQL_ASCII?

Would it not be better to take this as an opportunity to integrate ICU ?

That would work with both single and multibyte encodings.

... John

I know we can't currently use an index with non-C locales and LIKE
except when we create a sepcial type of index for LIKE indexing
(text_pattern_ops).

However, I am wondering if we should create a character lookup during
initdb that has the characters ordered so we can do:

col LIKE 'ha%' AND col >= "ha" and col <= "hb"

Could we do this easily for single-character encodings? We could have:

A 1
B 2
C 3

and a non-C locale could be:

A 1
A` 2
B 3

We can't handle multi-byte encodings because the number of combinations
is too large or not known.

Also, we mention you should use the "C" locale to use normal indexes for
LIKE but isn't it more correct to say the encoding has to be SQL_ASCII?

Why? "C" locale works well for multibyte encodings such as EUC-JP too.
--
Tatsuo Ishii

Bruce Momjian wrote:

However, I am wondering if we should create a character lookup during
initdb that has the characters ordered so we can do:

That won't work. Real-life collations are too complicated.

Also, we mention you should use the "C" locale to use normal indexes
for LIKE but isn't it more correct to say the encoding has to be
SQL_ASCII?

No, the locale decides the ordering.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Bruce Momjian wrote:

However, I am wondering if we should create a character lookup during
initdb that has the characters ordered so we can do:

That won't work. Real-life collations are too complicated.

OK.

Also, we mention you should use the "C" locale to use normal indexes
for LIKE but isn't it more correct to say the encoding has to be
SQL_ASCII?

No, the locale decides the ordering.

Oh, OK.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073