String collation and sort order in PostgreSQL

Introduction

String collation decides how text is sorted and compared in a database. It helps show words in the right order, like in a dictionary.

When you want to sort names alphabetically in a list.

When searching for words and you want the search to ignore accents or case.

When you need to compare strings in a way that matches local language rules.

When displaying sorted data to users from different countries.

When you want consistent sorting results regardless of how data was entered.

Syntax

PostgreSQL

SELECT column_name FROM table_name ORDER BY column_name COLLATE "collation_name";

Use COLLATE to specify which collation (sorting rule) to apply to a string column.

Collation names depend on your database and operating system settings.

Examples

Sorts the 'name' column using English (United States) rules.

PostgreSQL

SELECT name FROM users ORDER BY name COLLATE "en_US";

Sorts the 'city' column using German (Germany) rules.

PostgreSQL

SELECT city FROM locations ORDER BY city COLLATE "de_DE";

Sorts using the simple byte-wise order, which is fast but not language-aware.

PostgreSQL

SELECT product FROM items ORDER BY product COLLATE "C";

Sample Program

This example creates a temporary table with fruit names including accented letters. It then sorts them using English (US) collation, which treats accented letters close to their base letters.

PostgreSQL

CREATE TEMP TABLE fruits (name TEXT);
INSERT INTO fruits VALUES ('apple'), ('Äpple'), ('banana'), ('äpple');
SELECT name FROM fruits ORDER BY name COLLATE "en_US";

Important Notes

Different collations can sort the same words in different orders depending on language rules.

If you don't specify COLLATE, the database uses the default collation set when the database was created.

Using COLLATE can help make searches and sorts more natural for users in different languages.

Summary

String collation controls how text is sorted and compared in the database.

Use COLLATE in ORDER BY to apply specific language sorting rules.

Choosing the right collation helps show data in a way users expect.