String collation and sort order in PostgreSQL - Deep Dive

Overview - String collation and sort order

What is it?

String collation and sort order determine how text data is compared and arranged in a database. Collation defines the rules for character comparison, including case sensitivity and accent marks. Sort order is the sequence in which strings appear when sorted, influenced by collation. These rules affect searching, ordering, and grouping text data.

Why it matters

Without proper collation and sort order, text data might be sorted or compared incorrectly, causing confusion or errors. For example, names with accents or different cases might appear out of order or be treated as different when they should be the same. This impacts user experience, data accuracy, and application behavior.

Where it fits

Learners should first understand basic SQL queries and data types before exploring collation. After mastering collation, they can learn about indexing, performance optimization, and internationalization in databases.

Mental Model

Core Idea

Collation is the set of rules that tells the database how to compare and order text strings correctly.

Think of it like...

It's like sorting a list of names in a phone book where you decide if 'Émile' comes before or after 'Emily' based on language rules.

┌───────────────┐
│   Text Data   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Collation   │  ← Rules for comparing characters
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Sort Order   │  ← Resulting sequence of strings
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is Collation in Databases

Concept: Introduction to collation as rules for comparing text.

Collation defines how characters are compared and sorted. It decides if uppercase letters come before lowercase, or if accented letters are treated like their base letters. For example, in some collations, 'a' equals 'A', but in others, they are different.

Result

You understand that collation controls text comparison behavior in queries.

Understanding collation is key to controlling how text data behaves in sorting and searching.

2

Foundation

How Sort Order Depends on Collation

Concept: Sort order is the sequence of strings after applying collation rules.

When you sort text, the database uses collation rules to decide which string comes first. For example, sorting ['apple', 'Banana', 'ápple'] can yield different orders depending on collation settings, such as case sensitivity and accent handling.

Result

You see that changing collation changes the order of sorted text.

Sort order is not fixed; it depends entirely on the collation rules applied.

3

Intermediate

PostgreSQL Collation Support Basics

Concept: PostgreSQL uses operating system locales and supports multiple collations per database.

PostgreSQL allows you to set a default collation for the database, but you can also specify collation per column or per query. It uses the system's locale data to define collation rules, which means behavior can vary by server settings.

Result

You can control collation at different levels in PostgreSQL to affect sorting and comparison.

Knowing that collation can be set per column or query gives flexibility in handling diverse text data.

4

Intermediate

Using COLLATE Clause in Queries

🤔Before reading on: do you think specifying COLLATE in a query changes sorting only for that query or permanently changes the data? Commit to your answer.

Concept: The COLLATE clause overrides collation rules temporarily for a query or expression.

You can write queries like: SELECT name FROM users ORDER BY name COLLATE "en_US"; This sorts the 'name' column using the 'en_US' collation rules just for this query, without changing the stored data or column definition.

Result

The query returns rows sorted according to the specified collation.

Using COLLATE in queries allows temporary control over sorting without altering database schema.

5

Intermediate

Impact of Collation on Indexes and Performance

🤔Before reading on: do you think changing collation affects index usage or query speed? Commit to your answer.

Concept: Collation affects how indexes on text columns are built and used.

Indexes store data sorted by collation rules. If you query with a different collation than the index, the index might not be used efficiently, slowing down queries. Therefore, consistent collation between indexes and queries is important for performance.

Result

You understand that mismatched collations can cause slower queries due to index misses.

Knowing the link between collation and indexes helps optimize database performance.

6

Advanced

Custom Collations and ICU Support

🤔Before reading on: do you think PostgreSQL supports custom collations beyond system locales? Commit to your answer.

Concept: PostgreSQL supports creating custom collations using ICU, allowing fine control over sorting rules.

With ICU (International Components for Unicode), you can define collations that handle complex language rules, like ignoring accents or case in specific ways. You create these collations with CREATE COLLATION and then use them in tables or queries.

Result

You can tailor sorting behavior precisely for your application's language needs.

Custom collations unlock powerful internationalization and sorting control beyond defaults.

7

Expert

Surprising Effects of Collation on Query Results

🤔Before reading on: do you think collation affects only sorting, or can it also affect equality comparisons? Commit to your answer.

Concept: Collation influences not just sorting but also equality and pattern matching in queries.

When you compare strings with operators like = or LIKE, the collation rules determine if 'a' equals 'A' or if accented characters match. This means changing collation can change which rows appear in query results, not just their order.

Result

You realize collation impacts filtering and matching, affecting query correctness.

Understanding collation's role in comparisons prevents subtle bugs in data retrieval.

Under the Hood

PostgreSQL relies on the operating system's locale libraries or ICU to define collation rules. When comparing strings, it calls these libraries to determine character order and equivalence based on language-specific rules. Indexes store data sorted by these rules, and queries use them to compare and order text. Custom collations are wrappers around ICU rules allowing fine-tuned behavior.

Why designed this way?

Using system locales and ICU leverages existing, well-tested language rules without reinventing sorting logic. This design allows PostgreSQL to support many languages and adapt to new ones by updating locale data. Custom collations provide flexibility for special cases without changing core code.

┌───────────────┐
│  Text Input   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Collation API │  ← OS locale or ICU library
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Comparison &  │
│   Sorting     │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Indexes &   │
│   Query Plan  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does changing collation affect existing data storage or just how data is compared? Commit to yes or no.

Common Belief: Changing collation changes how data is stored on disk.

Reality: Collation affects only how data is compared and sorted, not how it is stored.

Why it matters: Misunderstanding this can lead to unnecessary data migrations or schema changes.

Quick: Do you think all collations treat uppercase and lowercase letters the same? Commit to yes or no.

Common Belief: All collations ignore case differences when sorting or comparing.

Reality: Many collations are case-sensitive, meaning 'A' and 'a' are different in sorting and comparison.

Why it matters: Assuming case-insensitivity can cause unexpected query results or sorting orders.

Quick: Does specifying COLLATE in a query permanently change the column's collation? Commit to yes or no.

Common Belief: Using COLLATE in a query changes the column's default collation permanently.

Reality: COLLATE in a query only affects that query or expression temporarily; it does not alter the column definition.

Why it matters: Confusing temporary overrides with permanent changes can cause confusion in database behavior.

Quick: Do you think collation only affects sorting, not equality checks? Commit to yes or no.

Common Belief: Collation affects only the order of strings, not whether two strings are equal.

Reality: Collation also affects equality and pattern matching, influencing which rows match queries.

Why it matters: Ignoring this can lead to bugs where queries miss or include unexpected rows.

Expert Zone

1

Some collations treat certain characters as equivalent for sorting but not for equality, causing subtle query differences.

2

PostgreSQL's use of ICU collations allows tailoring collation strength levels, like ignoring accents but respecting case.

3

Index usage depends on matching collation exactly; even small differences can cause index scans instead of index seeks.

When NOT to use

Avoid using complex custom collations when simple locale-based collations suffice, as they can add overhead. For binary or case-sensitive exact matching, use byte-wise comparison instead of locale collations.

Production Patterns

In production, developers often set database default collation for consistency, use COLLATE in queries for special cases, and create indexes with matching collations to optimize performance. ICU collations are used for multilingual applications requiring precise sorting.

Connections

Unicode Normalization

Builds-on

Understanding Unicode normalization helps explain why visually identical characters may sort differently depending on collation.

Internationalization (i18n)

Builds-on

Collation is a core part of internationalization, enabling software to handle sorting and comparison correctly across languages.

Human Language Phonetics

Analogous pattern

Just as phonetics defines how sounds relate in languages, collation defines how characters relate in sorting, showing a deep connection between language and data handling.

Common Pitfalls

#1 Assuming default collation fits all languages and changing it later is easy.

Wrong approach:

CREATE DATABASE mydb LC_COLLATE = 'en_US.UTF-8'; -- then later expecting it to sort French correctly

Correct approach:

Create database with appropriate collation for target language or use ICU collations for multilingual support.

Root cause:

Not understanding that collation is set at database creation and affects all sorting unless overridden.

#2 Using COLLATE in queries without matching index collation, causing slow queries.

Wrong approach:

SELECT * FROM users ORDER BY name COLLATE "fr_FR";

Correct approach:

Create index with matching collation: CREATE INDEX idx_name_fr ON users (name COLLATE "fr_FR");

Root cause:

Ignoring that indexes are built with specific collation and mismatches prevent index use.

#3 Expecting case-insensitive sorting without specifying a case-insensitive collation.

Wrong approach:

SELECT * FROM products ORDER BY product_name; -- default collation is case-sensitive

Correct approach:

SELECT * FROM products ORDER BY product_name COLLATE "en_US_ci"; -- case-insensitive collation

Root cause:

Not realizing default collations often consider case, affecting sort order.

Key Takeaways

Collation defines how text strings are compared and sorted in a database, affecting query results and order.

Sort order depends entirely on collation rules, which can vary by language and locale.

PostgreSQL allows setting collation at database, column, and query levels for flexible text handling.

Collation affects not only sorting but also equality and pattern matching, influencing which rows match queries.

Using appropriate collations and matching indexes is essential for correct behavior and good performance.

Practice

1. What does COLLATE do in a PostgreSQL query?

easy

A. It encrypts string data for security.

B. It controls how strings are sorted and compared based on language rules.

C. It changes the data type of a string column.

D. It creates an index on a string column.

Solution

1. Step 1: Understand COLLATE purpose

   COLLATE specifies the rules for sorting and comparing text, often based on language or locale.

2. Step 2: Differentiate from other functions

   COLLATE does not change data type, encrypt data, or create indexes; it only affects text ordering and comparison.

3. Final Answer:

   It controls how strings are sorted and compared based on language rules. -> Option B

4. Quick Check:

   COLLATE = sorting/comparison rules [OK]

Hint: COLLATE changes text sorting rules, not data type or encryption [OK]

Common Mistakes:

• Thinking COLLATE changes data type
• Confusing COLLATE with encryption
• Assuming COLLATE creates indexes

2. Which of the following is the correct syntax to sort a column name using the French collation in PostgreSQL?

easy

A. SELECT * FROM users ORDER BY COLLATE name 'fr_FR';

B. SELECT * FROM users ORDER BY name COLLATE fr_FR;

C. SELECT * FROM users ORDER BY name USING COLLATE 'fr_FR';

D. SELECT * FROM users ORDER BY name COLLATE 'fr_FR';

Solution

1. Step 1: Recall correct COLLATE syntax

   The correct syntax places COLLATE after the column name, followed by the collation name in single quotes.

2. Step 2: Check each option

   SELECT * FROM users ORDER BY name COLLATE 'fr_FR'; matches the correct syntax: ORDER BY name COLLATE 'fr_FR'. SELECT * FROM users ORDER BY COLLATE name 'fr_FR'; misplaces COLLATE. SELECT * FROM users ORDER BY name USING COLLATE 'fr_FR'; uses invalid keyword USING. SELECT * FROM users ORDER BY name COLLATE fr_FR; misses quotes around collation name.

3. Final Answer:

   SELECT * FROM users ORDER BY name COLLATE 'fr_FR'; -> Option D

4. Quick Check:

   ORDER BY column COLLATE 'locale' [OK]

Hint: Use ORDER BY column COLLATE 'locale' with quotes [OK]

Common Mistakes:

• Omitting quotes around collation name
• Placing COLLATE before column name
• Using USING keyword incorrectly

3. Given the table words with a column text containing values: 'apple', 'Äpple', 'banana', what is the result of this query?

SELECT text FROM words ORDER BY text COLLATE "C";

medium

A. ['Äpple', 'apple', 'banana']

B. ['apple', 'Äpple', 'banana']

C. ['apple', 'banana', 'Äpple']

D. ['banana', 'apple', 'Äpple']

Solution

1. Step 1: Understand COLLATE "C" behavior

   COLLATE "C" sorts based on byte values in UTF-8 encoding.

2. Step 2: Apply sorting to values

   Byte-wise: 'apple' starts with 0x61 ('a'), 'banana' with 0x62 ('b'), 'Äpple' with 0xC3 ('Ä'); 0x61 < 0x62 < 0xC3, so order is 'apple', 'banana', 'Äpple'.

3. Final Answer:

   ['apple', 'banana', 'Äpple'] -> Option C

4. Quick Check:

   COLLATE "C" sorts by byte order [OK]

Hint: COLLATE "C" sorts by byte values [OK]

Common Mistakes:

• Assuming locale-aware sorting with COLLATE "C"
• Thinking 'Äpple' comes before 'apple'
• Ignoring byte order sorting

4. You run this query but get an error:

SELECT * FROM products ORDER BY name COLLATE 'unknown_locale';

What is the likely cause?

medium

A. The collation 'unknown_locale' does not exist on the system.

B. The column name must be in double quotes.

C. COLLATE cannot be used in ORDER BY clause.

D. You must specify COLLATE before the column name.

Solution

1. Step 1: Identify error cause

   PostgreSQL throws an error if the specified collation name is not installed or recognized by the system.

2. Step 2: Check other options

   Column names do not require quotes unless case-sensitive; COLLATE is valid in ORDER BY; COLLATE must come after column name, which is correct here.

3. Final Answer:

   The collation 'unknown_locale' does not exist on the system. -> Option A

4. Quick Check:

   Invalid collation name causes error [OK]

Hint: Check collation name exists on system to avoid errors [OK]

Common Mistakes:

• Assuming COLLATE syntax is wrong
• Forgetting to install locale on OS
• Misplacing COLLATE keyword

5. You want to display a list of city names sorted according to German rules, but with case-insensitive sorting (e.g., 'Berlin' and 'berlin' treated the same). Which PostgreSQL approach is best?

hard

A. Use ORDER BY LOWER(city) COLLATE 'de_DE' ASC to sort case-insensitively with German collation.

B. Use ORDER BY city COLLATE 'de_DE' ASC and convert city to lowercase in SELECT.

C. Use ORDER BY city COLLATE 'C' ASC for fastest sorting.

D. Use ORDER BY city without COLLATE and rely on default sorting.

Solution

1. Step 1: Understand case-insensitive sorting need

   Sorting should ignore case but respect German language rules.

2. Step 2: Apply LOWER() with German collation

   Using LOWER(city) converts all to lowercase, then COLLATE 'de_DE' sorts according to German rules case-insensitively.

3. Step 3: Evaluate other options

   Use ORDER BY city COLLATE 'de_DE' ASC and convert city to lowercase in SELECT. sorts original case but does not affect sorting order; Use ORDER BY city COLLATE 'C' ASC for fastest sorting. uses byte sorting ignoring language rules; Use ORDER BY city without COLLATE and rely on default sorting. uses default locale which may not be German.

4. Final Answer:

   Use ORDER BY LOWER(city) COLLATE 'de_DE' ASC to sort case-insensitively with German collation. -> Option A

5. Quick Check:

   LOWER() + COLLATE for case-insensitive locale sort [OK]

Hint: Use LOWER() with COLLATE for case-insensitive locale sorting [OK]

Common Mistakes:

• Ignoring case by not using LOWER()
• Using COLLATE 'C' which ignores locale
• Relying on default locale for German sorting