>> 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. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
