PostgreSQL: Change String Case

I’ve dealt with enough inconsistent data to know that case matters more than you’d think. One record has “[email protected]”, another has “[email protected]”, and another has “[email protected]”—they’re all the same person, but they won’t match without careful comparison. When you’re displaying names to users, they expect “John Doe”, not “JOHN DOE” or “john doe”. Case conversion is one of those fundamental data cleaning operations you’ll use constantly.

PostgreSQL gives you three case conversion functions that solve different problems: UPPER() for all caps, LOWER() for all lowercase, and INITCAP() for title case. Understanding when to use each one will save you from subtle bugs and data consistency issues.

Quick Answer: Use UPPER(), LOWER(), INITCAP()

To change case:

SELECT
    name,
    UPPER(name) as uppercase,
    LOWER(name) as lowercase,
    INITCAP(name) as title_case
FROM users
WHERE name IS NOT NULL
LIMIT 5;

Returns:

name           | uppercase      | lowercase      | title_case
---------------+----------------+----------------+-----------
john DOE       | JOHN DOE       | john doe       | John Doe
MARY SMITH     | MARY SMITH     | mary smith     | Mary Smith

Each function handles case conversion differently: UPPER() makes everything uppercase, LOWER() makes everything lowercase, and INITCAP() capitalizes the first letter of each word.

How It Works

Here’s what’s happening under the hood with case conversion: PostgreSQL converts the internal representation of each character to a different case. When you call UPPER('john'), PostgreSQL looks up the uppercase version of each letter and returns ‘JOHN’. It does this for every character in the string.

The key thing to understand is that these functions work with whatever locale your PostgreSQL database is configured with. In English, it’s straightforward. But if you’re working with accented characters (like é, ñ, ü), the case conversion respects the language rules for that character. This is both good—you get correct behavior for international text—and something to be aware of if you’re doing comparisons.

INITCAP() is a bit special: it capitalizes the first letter of each word. PostgreSQL considers “words” to be separated by spaces and other non-alphanumeric characters. So INITCAP('hello-world') returns ‘Hello-World’, capitalizing after the hyphen too.

Different Approaches & When to Use Them

Approach 1: Standardize to Lowercase (Most Common)

The most common use case is converting everything to lowercase for case-insensitive comparisons:

SELECT
    email,
    LOWER(email) as normalized_email,
    phone,
    TRIM(LOWER(email)) as final_email
FROM users
WHERE email IS NOT NULL
ORDER BY LOWER(email);

I do this constantly with email addresses and usernames. Emails are supposed to be case-insensitive, so you normalize them to lowercase for storage and comparison. This prevents duplicates like “[email protected]” and “[email protected]” from being treated as different records.

Approach 2: Display Names (INITCAP)

When you’re displaying user-facing data, you want proper title case for names:

SELECT
    user_id,
    INITCAP(first_name) as first_name,
    INITCAP(last_name) as last_name,
    INITCAP(first_name) || ' ' || INITCAP(last_name) as full_name
FROM users
WHERE first_name IS NOT NULL;

This is what I use when building reports, user directories, or anywhere customers see the data. Nobody wants to see “JOHN DOE” or “john doe”—they expect “John Doe”. INITCAP() handles this automatically.

Approach 3: Uppercase for Codes and Categories

Sometimes you need everything in uppercase for codes, abbreviations, or standardized category names:

SELECT
    product_id,
    product_name,
    UPPER(category) as category_code,
    UPPER(country_code) as country,
    UPPER(sku) as standardized_sku
FROM products
WHERE category IS NOT NULL;

I use UPPER() for codes that should be standardized. Country codes are always uppercase. Product SKUs are often standardized to uppercase. This prevents mixing “us” and “US” in your lookup tables.

Approach 4: Case-Insensitive Search with LOWER()

When you need to search regardless of how the user types it:

SELECT
    user_id,
    email,
    first_name,
    last_name
FROM users
WHERE LOWER(email) = LOWER('[email protected]')
OR LOWER(first_name) = LOWER('JOHN');

The user might search for “john”, “JOHN”, or “John”. By converting both sides to lowercase, you catch all variations. This is exactly how real search forms work.

Approach 5: Combined Case Normalization

Real-world scenarios often combine multiple case functions with trimming and formatting:

SELECT
    user_id,
    INITCAP(TRIM(first_name)) as first_name,
    INITCAP(TRIM(last_name)) as last_name,
    LOWER(TRIM(email)) as email,
    UPPER(TRIM(country_code)) as country
FROM user_import
WHERE TRIM(email) IS NOT NULL;

This is what production import code looks like. You’re trimming whitespace, normalizing case, and ensuring consistency across multiple fields.

Real-World Example: User Directory with Proper Formatting

Let me walk you through a realistic scenario. You’re building a user directory and need to display names properly while maintaining case-insensitive search capability:

SELECT
    user_id,
    INITCAP(TRIM(first_name)) as first_name,
    INITCAP(TRIM(last_name)) as last_name,
    INITCAP(TRIM(first_name)) || ' ' || INITCAP(TRIM(last_name)) as display_name,
    LOWER(TRIM(email)) as email,
    LOWER(TRIM(username)) as username,
    UPPER(TRIM(department)) as department,
    created_at
FROM users
WHERE active = true
AND LOWER(TRIM(email)) IS NOT NULL
ORDER BY LOWER(TRIM(last_name)), LOWER(TRIM(first_name));

Here you’re doing real work: displaying names in title case for the UI, normalizing emails and usernames to lowercase for searches and uniqueness checking, standardizing department codes to uppercase, and sorting by normalized names. This is exactly the pattern you’d use building a real user directory.

FAQ

Q: Does case conversion affect accented characters like é, ñ, ü? A: Yes, and correctly. PostgreSQL respects locale rules. UPPER('café') returns ‘CAFÉ’ with the accent preserved. This works great for international text, but be aware if you’re doing comparisons across different locales.

Q: How do I do custom title case where only the first letter is capitalized? A: Use UPPER(SUBSTRING(text, 1, 1)) || LOWER(SUBSTRING(text, 2)) to capitalize only the first character and lowercase the rest. For example: UPPER(SUBSTRING('JOHN DOE', 1, 1)) || LOWER(SUBSTRING('JOHN DOE', 2)) returns ‘John doe’.

Q: What’s the performance impact of case conversion on large datasets? A: Very fast. These are native, optimized operations. Converting case on millions of rows has negligible overhead. Don’t worry about optimization.

Q: Does INITCAP handle multiple spaces correctly? A: Yes. INITCAP('hello world') correctly produces ‘Hello World’ with spacing preserved. It’s smart about finding word boundaries.

Q: Are comparisons case-sensitive by default in PostgreSQL? A: Yes. 'john' = 'John' returns false. This is why you need LOWER() or UPPER() for case-insensitive comparison. However, you can also create case-insensitive indexes using COLLATE for specific columns.

Q: Can I use case conversion in ORDER BY? A: Yes, absolutely. ORDER BY LOWER(name) sorts case-insensitively, which is usually what you want for user names. Without it, uppercase letters sort before lowercase in ASCII order.

Q: What happens when converting non-alphabetic characters (numbers, symbols)? A: They’re unaffected. UPPER('hello123!@#') returns ‘HELLO123!@#’—only alphabetic characters change. Numbers and symbols stay exactly the same.

Q: Should I store data in lowercase, or convert it when needed? A: Store it in normalized form (lowercase for emails and usernames) and convert for display. This is more efficient and cleaner—you do the normalization once on import, not repeatedly on every query.

Q: How do I handle names with apostrophes like O’Brien? A: INITCAP() handles this correctly: INITCAP('o\'brien') returns ‘O’Brien’. It capitalizes after spaces and some punctuation. However, you might need to verify behavior with specific punctuation in your locale.

Q: Can I combine case conversion with pattern matching? A: Yes. WHERE LOWER(email) LIKE LOWER('%@gmail%') does case-insensitive pattern matching. Just convert both sides the same way.

Q: What’s the best practice for storing email addresses? A: Store them in lowercase. Email addresses are technically case-insensitive in their local part (before @), so normalizing to lowercase prevents duplicates. Use LOWER(TRIM(email)) when inserting or updating.

Common Errors & Solutions

Error: “Case conversion returns unexpected results for accented characters” This might be a locale issue. Check your database locale with SHOW LC_COLLATE. If you’re dealing with specific locales, PostgreSQL respects locale-specific case conversion rules, which might differ from what you expect.

Error: “Comparison with LOWER() seems slow” If you’re doing WHERE LOWER(column) = value on millions of rows, it won’t use indexes efficiently. Instead, store data already normalized (lowercase) and index on the normalized column. Or create a computed index: CREATE INDEX idx_lower_email ON users (LOWER(email)).

Error: “INITCAP doesn’t capitalize as expected” Remember: INITCAP() capitalizes the first letter of each word. It might capitalize after punctuation you didn’t expect. Test with your actual data to verify behavior.

Performance Tips

  • Very fast operation: Case conversion is native and optimized. Use it freely without worrying about overhead.
  • Store normalized data: Instead of converting on every query, normalize data on import. Store lowercase for emails, uppercase for codes.
  • Use indexes on normalized columns: If you search on lowercased values, create an index on LOWER(column): CREATE INDEX idx_email ON users (LOWER(email)).
  • Combine efficiently: INITCAP(TRIM(name)) is optimized as a single operation. Chaining functions is fine.
  • Case-insensitive comparisons: Always use LOWER() or UPPER() on both sides of comparisons, or use case-insensitive collations for columns that need it.

Wrapping Up

Case conversion is one of those fundamental operations you’ll use constantly. Normalize data to lowercase on import for consistency and efficient searching, display it in title case using INITCAP() for users, and standardize codes to uppercase. The key is understanding that these functions are fast, reliable, and respect locale-specific rules for international text. Make case normalization part of your data import pipeline, and you’ll have cleaner, more queryable data from day one.