Case Insensitive ORDER BY clause using COLLATE
5
votes
3
answers
13958
views
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.
Asked by Manngo
(3145 rep)
Nov 22, 2020, 04:44 AM
Last activity: Jan 24, 2025, 12:49 PM
Last activity: Jan 24, 2025, 12:49 PM