>> SELECT * FROM foo WHERE bar::CHAR(3)='ABC';
> Your query is the same as using LIKE, so why not express it that way?
I want simply to select by first 3 characters. LIKE is too powerful and
unnessecary. LIKE requires
escaping % and ? characters in pattern.
I expected that making planner to use primary key index in case of
WHERE bar::CHAR(3)='ABC'
or in
WHERE bar BETWEEN 'ABC' AND 'ABC' || CHR(255)
or in
WHERE SUBSTRING( bar FOR 3 )='ABC'
is more simpler than dealing with locale problems in WHERE bar LIKE 'ABC%':
Using LIKE with index optimization in Postgres in non-C locale requires
a. creating non-SQL standard compatible index unsin operator class
b. requires that primary key column has two indexes
This is too much overhead.
>Is it that unreasonable that a PRIMARY KEY should use the most natural
> way to order strings for your locale
This is very reasonable. PRIMARY KEY must use locale order always.
> and that if you want to use LIKE
> in non-C locales that you need to specify that explicitly?
This is unreasonable.
If I use SQL standard way to create table
CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY );
and use SQL standard WHERE clause
WHERE bar LIKE 'ABC%'
or
WHERE bar::CHAR(3)='ABC'
I expect that primary key index can be used without non-standard extensions
to SQL language
Andrus.
