String collation and sort order in PostgreSQL - Step-by-Step Execution

Concept Flow - String collation and sort order

Start with string list

↓

Apply collation rules

↓

Compare strings by collation

↓

Sort strings based on comparison

↓

Return sorted list

Strings are sorted by comparing characters according to collation rules, which define language-specific order.

Execution Sample

PostgreSQL

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

This code inserts fruit names and sorts them using English (US) collation rules.

Execution Table

Step	Action	String List State	Comparison Detail	Resulting Order
1	Insert 'apple'	['apple']	N/A	['apple']
2	Insert 'Banana'	['apple', 'Banana']	N/A	['apple', 'Banana']
3	Insert 'ápple'	['apple', 'Banana', 'ápple']	N/A	['apple', 'Banana', 'ápple']
4	Insert 'banana'	['apple', 'Banana', 'ápple', 'banana']	N/A	['apple', 'Banana', 'ápple', 'banana']
5	Sort by name COLLATE "en_US"	['apple', 'Banana', 'ápple', 'banana']	Compare 'Banana' vs 'banana': case-insensitive, 'Banana' = 'banana' Compare 'apple' vs 'ápple': 'a' < 'á'	['apple', 'Banana', 'banana', 'ápple']

💡 Sorting complete using en_US collation rules.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
fruits list	[]	['apple']	['apple', 'Banana']	['apple', 'Banana', 'ápple']	['apple', 'Banana', 'ápple', 'banana']	['apple', 'Banana', 'banana', 'ápple']

Key Moments - 2 Insights

Why does 'ápple' come after 'apple' in the sorted list?

Because the collation treats accented characters like 'á' as different and sorts them after 'a', as shown in step 5 of the execution_table.

Why is 'Banana' sorted before 'banana' even though they look similar?

The collation is case-insensitive, so 'Banana' and 'banana' are considered equal in sorting order, but the original order is preserved, as seen in step 5.

Visual Quiz - 3 Questions

Look at the execution_table at step 5, which string comes first after sorting?

A 'ápple'

B 'apple'

C 'Banana'

D 'banana'

💡 Hint

Check the 'Resulting Order' column in step 5 of the execution_table.

At which step does the list first contain 'ápple'?

A After 3

B After 1

C After 2

D After 4

💡 Hint

Look at the 'String List State' column in the execution_table rows 3 and 4.

If we changed collation to "C" (binary), how would 'Banana' and 'banana' be sorted?

A 'banana' before 'Banana'

B They would be considered equal

C 'Banana' before 'banana'

D Sorting would fail

💡 Hint

Binary collation sorts by byte value, uppercase letters come before lowercase.

Concept Snapshot

String collation defines how text is compared and sorted.
Use COLLATE clause to specify collation in ORDER BY.
Collation affects case sensitivity and accented characters.
Different locales have different sorting rules.
Without COLLATE, default database collation is used.

Full Transcript

This visual execution shows how PostgreSQL sorts strings using collation. We start with an empty list and insert four fruit names: 'apple', 'Banana', 'ápple', and 'banana'. The list grows step by step. When sorting with COLLATE "en_US", PostgreSQL compares strings according to English US rules. Accented characters like 'á' come after 'a', so 'ápple' sorts after 'apple'. Case differences are handled so that 'Banana' and 'banana' are considered equal in sorting order, preserving their original order. The final sorted list is ['apple', 'Banana', 'banana', 'ápple']. This example helps understand how collation affects string order in databases.

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