![]() ![]() ![]() 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. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values. 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. Otherwise, any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.īut 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), 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. The following statement from the Collation Support documentation for version 12 explains that deterministic needs to be set to false: 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: 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.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.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).The first four dbfiddle example queries with an ORDER BY clause show three different sort results: PostgreSQL nondeterministic collations are not supported for LIKE This solution is similar to what worked for this related answer (also on DBA.SE): Instead of sqlfiddle, I used dbfiddle, which offers several versions of PostgreSQL, for the example: 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) The default collation for my database is en_US.UTF-8. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. I have a test fiddle on !17/05cab/1, but it’s only for PostgreSQL 9.6. 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.įWIW, I am testing on PostgreSQL 11.8. Is there a COLLATE clause value that would sort Case Insensitive? I have pored over the documentation and searched high and low, but I can’t find anything so straightforward for PostgreSQL. MSSQL: Latin1_General_BIN | Latin1_General_CI_AS MariaDB: utf8mb4_bin | utf8mb4_general_ci In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause: SELECT * I have spent a long time looking for this, and I am getting mixed messages.
0 Comments
Leave a Reply. |