5

I have spent a long time looking for this, and I am getting mixed messages.

In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause:

SELECT *
FROM orderby
ORDER BY string COLLATE … ;

--  SQLite:     BINARY | NOCASE
--  MariaDB:    utf8mb4_bin | utf8mb4_general_ci
--  Oracle:     BINARY | BINARY_CI
--  MSSQL:      Latin1_General_BIN | Latin1_General_CI_AS

I have pored over the documentation and searched high and low, but I can’t find anything so straightforward for PostgreSQL.

Is there a COLLATE clause value that would sort Case Insensitive?

I know there are many questions regarding case sensitivity, but (a) most of them are old and (b) none that I have seen relate to the COLLATE clause.

FWIW, I am testing on PostgreSQL 11.8. I have a test fiddle on http://sqlfiddle.com/#!17/05cab/1, but it’s only for PostgreSQL 9.6.

MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.

7
  • 1
    Take care playing with collation, or applying other transforms/functions in you ORDER BY clause, unless you know the statement has filtering clauses that mean the result set is small (and that the query planner can use those filters effectively) because the transform will force a full sort. If returning many rows, or having to consider many rows due to top/limit/offset that is applied after the sort, this could be expensive and even involve s spool to disk. Nov 23, 2020 at 3:13
  • @DavidSpillett Well, that's the case for all RDBMS. An index can only support a query if the collations match. Nov 23, 2020 at 7:19
  • 1
    The default collation for my database is en_US.UTF-8: this collation already sorts case-insensitively. Can you add an example of the output you get versus the output you expect with an ORDER BY clause with this collation? Nov 23, 2020 at 11:23
  • @LaurenzAlbe - correct (with the exception of those which support function based indexes which would allow you to workaround the issue at the expense of some space), though I've encountered people who don't realise that it is the case for collation changes, or see an index being used and assume all is as they intend (not noticing it is being scanned rather than for seeking). Nov 24, 2020 at 15:58
  • @DanielVérité (and others): The en_US.utf8 collation does not sort case-insensitively. It's mostly case-sensitive (not 100%, but for all practical purposes). The first db<>fiddle link in my answer (using version 12) shows the difference, especially with the 4 "banana" rows. Of course, I'm still not sure why someone would request case-insensitive sorting but not comparisons / filtering when that would be much more useful. In fact, I can't think of a reason to ask specifically for a case-insensitive sort, unless you are trying to introduce some amount of randomness for testing. Nov 25, 2020 at 9:39

3 Answers 3

4

If you were using PostgreSQL 12 or newer you would be able to create a new collation via the CREATE COLLATION command that specifies deterministic = false:

CREATE COLLATION ci (provider = icu, locale = 'en-US-u-ks-level2', deterministic = false);

Instead of sqlfiddle, I used db<>fiddle, which offers several versions of PostgreSQL, for the example:

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a8f4b330d04266947fb11e5c04fa4891&hide=2

This solution is similar to what worked for this related answer (also on DBA.SE):
PostgreSQL nondeterministic collations are not supported for LIKE

The first four db<>fiddle example queries with an ORDER BY clause show three different sort results:

  1. the ORDER BY "string" COLLATE "POSIX" query returns rows in closer to a binary / ordinal ordering with all upper-case (A - Z) grouped before all lower-case (a - z).
  2. the ORDER BY "string" and ORDER BY "string" COLLATE "en_US.utf8" queries return rows in what is effectively case-sensitive order with "A" and "a" grouped together, and lower-case of each letter coming before the upper-case of the same letter (aA bB ...).
  3. the ORDER BY "string" COLLATE "ci" query returns rows in a truly case-insensitive order with "A" and "a" grouped together, but this time the cases of each particular letter are not in a predefined order and the two cases can even be intermixed within a letter. Since it is not guarantee to produce the same order each time, here is a sample of the result set that I'm currently seeing for this query:
    -8    banana
    -15   Banana
    -9    bANana
    1116  Banana
    

The following statement from the Collation Support documentation for version 12 explains that deterministic needs to be set to false:

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), in order for such collations to act in a truly case- or accent-insensitive manner, they also need to be declared as not deterministic in CREATE COLLATION;... Otherwise, any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

But the same note from the same Collation Support documentation for version 11 explains that sorting is still effectively case-sensitive:

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

Taking a closer look at the CREATE COLLATION documentation, it seems that the deterministic property (which defaults to true but needs to be set to false to do insensitive comparisons and sorting) was introduced in Version 12, which doesn't really help you. (@LaurenzAlbe recommended an alternate syntax in a comment that might work on older versions of libicu, and adjusting the db<>fiddle test linked above for that syntax and to run on PostgreSQL 11, it does not appear to work: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c3c48e111ed1837987524fee2c54a183&hide=2 )

That leaves you with two options:

  1. lower() function
  2. citext datatype (as mentioned by @a_horse_with_no_name in a comment on the question)

The citext datatype calls the lower() function internally, but has the following benefits (according to the documentation, linked directly above):

  1. it applies to implicit indexes created by UNIQUE and PRIMARY KEY constraints
  2. don't need to remember to add lower() to both sides of a comparison

The two main considerations are:

  • citext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. It is, however, slightly more efficient than using lower to get case-insensitive matching.

  • citext doesn't help much if you need data to compare case-sensitively in some contexts and case-insensitively in other contexts. The standard answer is to use the text type and manually use the lower function when you need to compare case-insensitively; this works all right if case-insensitive comparison is needed only infrequently. If you need case-insensitive behavior most of the time and case-sensitive infrequently, consider storing the data as citext and explicitly casting the column to text when you want case-sensitive comparison. In either situation, you will need two indexes if you want both types of searches to be fast.

It sounds like citext creates a lower-case copy of the data that takes up more space but is slightly faster due to the pre-calculation.

Also, citext needs to be installed. I modified the earlier example queries to make use of citext to show what can be done on PostgreSQL 11, but it seems that citext is not installed on db<>fiddle. I will put in a request for that. But here is are the test queries that should show how it works on your system if you have citext installed:

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=7dd95430aad8f646bfa1b7e79e21871c&hide=2

All of that being said, it is important to note that case-insensitive ordering, for the only or right-most / final column, isn't much different than case-sensitive ordering: the same letters across both cases are going to group together. It's just that with case-sensitive ordering, each case within each letter will group together (i.e. all "A"s before or after all "a"s), while case-insensitive ordering allows for "A"s and "a"s to be intermixed (since neither has priority). Allowing different cases of the same letter to intermix becomes important when there is at least one additional sort criteria in the ORDER BY list, specified to the right of (i.e. after) the string column in question. The final two example queries in the db<>fiddle linked at the top of this answer shows the difference.

With all of this in mind, the main question is: what precisely are you trying to accomplish? The request, as it is currently stated in the question, is for a case-insensitive sort. Does this mean that the comparisons should still be case-sensitive? That would seem like an odd combination given that it is fairly common to have/want a case-insensitive comparison, but the sort is less important as there is often little difference between case-sensitive and insensitive sorting (case-sensitive is consistent, but case-insensitive will have random ordering of strings that differ only in casing, and that randomness sometimes results in the same ordering as what case-sensitive would return), unless there are additional fields being sorted on.

Comment from the O.P.:

MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.

It's still not clear why any "gaps" need to be filled, but it sounds like there is a slight issue with terminology: the term "sort" / "sorting" is being used, but I suspect that the main issue is with comparisons, given that there's often no visible difference between case -sensitive and -insensitive when it comes to sorting. And, without overriding a predicate or ORDER BY field/expression with a COLLATE clause, the same sensitivity (or lack thereof) would be applied to both. This is important because, if I'm correct that O.P. truly means both, then:

  1. Performance-wise, I would guess that lower might actually be slightly better than casting to citext in this specific context because citext is going to call lower anyway but is also doing a datatype conversion. To be fair, I haven't done any testing around this so it's merely an edumacated guess, assuming that when used as a datatype for a column, citext is slightly better due to the values being pre-calculated, but in this case there's no opportunity to precalculate.
  2. Using either citext or lower in the ORDER BY will only solve half the issue (and the less important half given, again, that the distinction is barely noticable)
  3. Most likely you need to stop focusing on the ORDER BY clause and use citext as the datatype for the columns, which will take care of both comparisons and sorting.
5
  • 1
    Thanks for the tip on dbfiddle. I was able to test this on various current versions. The main downside with your solution is that I believe that it requires version 12 to create new collations. That’s not a deal breaker, but it would be interesting to see how far back I can go. I also found this collation: COLLATE "en-x-icu" which seems to work down to version 10.
    – Manngo
    Nov 22, 2020 at 21:36
  • @Manngo I do not believe that is true. The documentation I linked to is for Version 11, and the db<>fiddle test works on Version 11 if you remove the , deterministic=false part of the locale in CREATE COLLATION (though it can't be case-insensitive unless deterministic is false). However, it does not appear to be acting as case-insensitive. Also, I don't think that "en-x-icu" is going to help as it's not case-insensitive. Nov 22, 2020 at 21:55
  • For older versions of libicu, you might need to use en_US@colStrength=secondary. Nov 23, 2020 at 7:23
  • 1
    @LaurenzAlbe Thanks, I had forgotten about that syntax. I tried it but it didn't help. My initial attempt didn't work on version 12 because of a typo in the locale that Daniel Vérité caught. For version 12 the case-insensitive collation now works as expected. It still doesn't in version 11, but that's also expected base on the documentation saying that case-insensitive sorting isn't support in version 11 (at least not though a collation). I've updated my answer to reflect that. Nov 25, 2020 at 8:45
  • @Manngo I just made some updates related to when case-insensitive sorting is relevant. Please review. Thanks :-) (P.S. Go here if you aren't sure what changed). Dec 12, 2020 at 23:17
3

Another suggestion is that you use GENERATED COLUMNS as follows.

I do know that this is potentially wasteful of space, but once PostgreSQL implements the VIRTUAL storage mechanism for GENERATED COLUMNS, this shouldn't be (too much of a) problem. The one fly in the ointment for this question is that they're only available only PostgreSQL version >= 12 - this is more of a potential suggestion.

On the plus side, the GENERATED COLUMN solution has the advantage of not requiring an extension - often not possible in fiddles, but more importantly as a consultant, installs of extensions and other software are frequently not permitted.

So, I did the following (see fiddle here):

CREATE TABLE test
(
  num SERIAL PRIMARY KEY,
  the_string TEXT NOT NULL,
  the_string_ci TEXT GENERATED ALWAYS AS (LOWER (the_string)) STORED
);

Populate it:

INSERT INTO test (the_string)
VALUES
('Apple'), ('apple'), 
('Orange'), ('orange'), 
('Banana'), ('banana'), 
('Pear'), ('pear');

And then ran this query - case sensitive:

SELECT * FROM test WHERE the_string = 'Apple';

Result:

num the_string  the_string_ci
  1      Apple          apple

And the case insensitive query:

SELECT * FROM test WHERE the_string_ci = 'apple';

Result:

num the_string  the_string_ci
  1      Apple          apple
  2      apple          apple
1

Is there a COLLATE clause value that would sort Case Insensitive?

en_US.UTF-8 as provided by the GNU C library does linguistic-aware comparisons that are meant to sort strings independently of the case.

Here's an example on Ubuntu 18.04, PostgreSQL 11.

=> with a(x) as (values ('a'),('A'), ('b'),('B'),('c'),('C'))
select * from a order by x collate "en_US.utf8";

 x 
---
 a
 A
 b
 B
 c
 C

On the other hand, the "C" collation does a binary sort:

=> with a(x) as (values ('a'),('A'), ('b'),('B'),('c'),('C'))
select * from a order by x collate "C";
 x 
---
 A
 B
 C
 a
 b
 c

With the "C" collation, the upper case letters come out grouped together because their Unicode codepoints are less than the codepoints of the lower case counterparts. As opposed to that, en_US.utf8 does linguistic-aware comparisons that group together letters that differ only by the case.

The logic that PostgreSQL implements to compare strings could be summarized like this:

IF the collation is "C" THEN  
   result := strcmp(str1, str2)  (binary comparison)  
ELSE  
  IF it's an ICU collation THEN  
    result :=  ucol_strcoll(str1, str2)  
  ELSE  
    result := strcoll(str1, str2) (see [1] below)
  END IF;
  IF result is "equality" and the collation is deterministic THEN  
    result := strcmp(str1, str2) (tie-breaker binary comparison)  
  END IF  
END IF

[1] strcoll Note that strcoll and ucol_strcoll are provided by external libraries. PostgreSQL does not embed the linguistic rules to compare strings, contrary to many other databases, and notably MariaDB/MySQL and Oracle.

When using en_US.UTF-8 from GNU libc, a and A are not equal, since this collation is case-sensitive. But since a < A < b < B, you get the same effect as if the collation was case-insensitive in terms of grouping together strings by the base letter. If you're using a less-capable libc such as musl libc, you might get different results.

As another example with a truly case-insensitive comparison (requires PostgreSQL 12 and an ICU-enabled build):

#= CREATE COLLATION ci (provider = icu, locale = 'en-US-u-ks-level2', deterministic=false);

=# select 'a'='A' collate ci;
 ?column? 
----------
 t

=# with a(x) as (values ('a'),('A'), ('b'),('B'),('c'),('C'))
   select * from a order by x collate ci;
 x 
---
 a
 A
 b
 B
 c
 C

So the result with a truly case-insensitive collation is the same as with en_US.UTF-8 with GNU libc as the provider. In that sense, it's unclear why the question claims that a case-insensitive collation would sort better than en_US.UTF-8.

2
  • Hi Daniel. Thanks for catching that typo in my CREATE COLLATION example. I made sure to note it in the edit comment. Everything is now working as expected :). Interesting to learn that there are differences in different implementations of libc. Nov 25, 2020 at 9:05
  • Also, and please understand that I'm only trying to be helpful, but I believe your presentation of case-insensitivity is incorrect. The effect of case-insensitivity on sorting is often subtle or entirely unnoticeable, but it's definitely not the same as case-sensitive. The documentation implies it, and my db<>fiddle example proves it. Case-sensitive sorting is guaranteed order between cases of the same letter: (a,a,A,A,b), case-insensitive sorting is not: (a,A,a,A,b), (A,a,a,A,b), etc, or even (a,a,A,A,b). Insensitive can produce same result as sensitive, but that's purely coincidental. Nov 25, 2020 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.