String collation and sort order in PostgreSQL - Mini Project: Build & Apply

String Collation and Sort Order in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to list book titles in different languages and see how sorting changes with different collations.

🎯 Goal: Create a table with book titles, insert sample data with mixed language characters, and write queries to sort the titles using different collations.

📋 What You'll Learn

Create a table called books with a column title of type TEXT

Insert exactly these titles: 'Zebra', 'apple', 'Äpple', 'banana', 'ångström'

Write a query to select all titles sorted by default collation

Write a query to select all titles sorted by en_US.utf8 collation

Write a query to select all titles sorted by sv_SE.utf8 collation

💡 Why This Matters

🌍 Real World

Sorting text data correctly is important in applications like bookstores, libraries, or any system that displays lists of names or titles in multiple languages.

💼 Career

Understanding string collation and sort order helps database professionals ensure data is presented in a user-friendly and culturally appropriate way.

1

Create the books table

Create a table called books with a single column title of type TEXT.

PostgreSQL

-- Create the books table with a title column
-- Your code here

Need a hint?

Use CREATE TABLE books (title TEXT); to create the table.

2

Insert book titles into books

Insert these exact titles into the books table: 'Zebra', 'apple', 'Äpple', 'banana', 'ångström'.

PostgreSQL

CREATE TABLE books (title TEXT);
-- Insert the specified titles into books
-- Your code here

Need a hint?

Use INSERT INTO books (title) VALUES ('Zebra'), ('apple'), ('Äpple'), ('banana'), ('ångström');

3

Query titles sorted by default collation

Write a query to select all title values from books sorted by title using the default collation.

PostgreSQL

CREATE TABLE books (title TEXT);
INSERT INTO books (title) VALUES
('Zebra'),
('apple'),
('Äpple'),
('banana'),
('ångström');

-- Select titles sorted by default collation
-- Your code here

Need a hint?

Use SELECT title FROM books ORDER BY title; to sort by default collation.

4

Query titles sorted by en_US.utf8 and sv_SE.utf8 collations

Write two queries: one to select all title values sorted by title COLLATE "en_US.utf8", and another sorted by title COLLATE "sv_SE.utf8".

PostgreSQL

CREATE TABLE books (title TEXT);
INSERT INTO books (title) VALUES
('Zebra'),
('apple'),
('Äpple'),
('banana'),
('ångström');

SELECT title FROM books ORDER BY title;

-- Select titles sorted by en_US.utf8 collation
-- Your code here

-- Select titles sorted by sv_SE.utf8 collation
-- Your code here

Need a hint?

Use ORDER BY title COLLATE "en_US.utf8" and ORDER BY title COLLATE "sv_SE.utf8" to sort with different collations.