Thread: Non-C locale and LIKE
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 1B 2C 3 and a non-C locale could be: A 1A` 2B 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? -- 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, Pennsylvania19073
> 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, Pennsylvania19073