3 ms·
(I make this a reply, as it is kind of a separate thought; also, I just came up with this in the shower, and a ton of people have already read the other comment
by saurik 15y ago
(I make this a reply, as it is kind of a separate thought; also, I just came up with this in the shower, and a ton of people have already read the other comment and might not notice a new edit even if they cared about the post. ;P)
As some people may balk at the "english_collation_less" operator usage (after all: using < is so much more convenient, especially if you have multiple of these operators strewn throughout every single one of your statements), all we really need to specify is a single new data type: collated_text. This, combined with a "client_collation" session variable, would allow clients to use < and ORDER BY on fields of all types, and get the behavior they would expect. It is only while constructing an index that you would need to specify the collation using a more complex syntax.
CREATE TABLE my_table (a collated_text);
CREATE INDEX my_index ON my_table (a collation_ops('en_US'));
SET client_collation = 'en_US';
SELECT * FROM my_table WHERE a < 'Bob' ORDER BY a;
(Given that they already seemed willing to add special syntax for per-column collation, if you did not believe in the dream of "per-operator class parameters" (or "parameterized operator classes", or "meta operator classes", or whatever), you could imagine "collation_ops('en_US')" being replaced by an index-specific syntax COLLATED BY 'en_US'). Just saying there are tons of options here, and they all seem better than forcing me to have 7x as much data in each of my rows, all identical.)