Sample Header Ad - 728x90

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