String collation and sort order in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Sorting strings with default collation

Given the table fruits with a column name containing the values: 'apple', 'Banana', 'cherry', 'Apricot', what is the order of names returned by this query?

SELECT name FROM fruits ORDER BY name;

PostgreSQL

CREATE TABLE fruits (name TEXT);
INSERT INTO fruits (name) VALUES ('apple'), ('Banana'), ('cherry'), ('Apricot');
SELECT name FROM fruits ORDER BY name;

A ["Banana", "Apricot", "apple", "cherry"]

B ["apple", "Apricot", "Banana", "cherry"]

C ["Apricot", "Banana", "apple", "cherry"]

D ["apple", "Banana", "Apricot", "cherry"]

💡 Hint

PostgreSQL default collation sorts uppercase letters before lowercase letters.

By default, PostgreSQL sorts strings using the database's collation, which places uppercase letters before lowercase letters. So 'Apricot' and 'Banana' come before 'apple' and 'cherry'.

❓ query_result

intermediate

Using COLLATE to change string sort order

What is the order of names returned by this query?

SELECT name FROM fruits ORDER BY name COLLATE "C";

Given the same fruits table as before.

PostgreSQL

SELECT name FROM fruits ORDER BY name COLLATE "C";

A ["Apricot", "Banana", "apple", "cherry"]

B ["Banana", "Apricot", "apple", "cherry"]

C ["apple", "Banana", "Apricot", "cherry"]

D ["apple", "Apricot", "Banana", "cherry"]

💡 Hint

The "C" collation sorts purely by byte value, uppercase letters (65-90) before lowercase (97-122).

The "C" collation sorts strings by ASCII byte values, so uppercase letters (like 'A'=65) come before lowercase letters (like 'a'=97). Same order as default.

📝 Syntax

advanced

Identify the syntax error in COLLATE usage

Which option contains a syntax error when trying to order by a column with a specific collation in PostgreSQL?

PostgreSQL

SELECT name FROM fruits ORDER BY name COLLATE 'en_US';

A SELECT name FROM fruits ORDER BY name COLLATE "en_US";

B SELECT name FROM fruits ORDER BY name COLLATE 'en_US';

C SELECT name FROM fruits ORDER BY name COLLATE en_US;

D SELECT name FROM fruits ORDER BY name COLLATE 'en-US';

💡 Hint

PostgreSQL requires double quotes for collation names, not single quotes.

In PostgreSQL, collation names must be double-quoted identifiers, not string literals. Using single quotes causes a syntax error.

🧠 Conceptual

advanced

Effect of collation on string comparison

Which statement about string comparison with different collations in PostgreSQL is true?

A Comparing strings with different collations always returns an error.

B Collation has no effect on string operations in PostgreSQL.

C Collation affects only sorting order, not equality comparison.

D Collation affects both sorting order and equality comparison results.

💡 Hint

Think about how collation defines character order and equivalence.

Collation defines how strings are compared, affecting both sorting and equality checks. Different collations can treat characters as equal or different.

❓ optimization

expert

Optimizing queries with collations for performance

You have a large table with a text column frequently sorted using a non-default collation. Which approach improves query performance the most?

A Add an index on the text column using the non-default collation.

B Use ORDER BY with COLLATE in every query without indexes.

C Convert the column to lowercase before sorting without indexes.

D Create a materialized view sorted by the column without indexes.

💡 Hint

Indexes can speed up sorting if they match the collation used.

Creating an index on the column with the specific collation allows PostgreSQL to use the index for sorting, greatly improving performance.