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.