PostgreSQL LOWER and UPPER Functions

The LOWER() and UPPER() functions in PostgreSQL convert strings to lowercase and uppercase respectively. These functions are essential for case-insensitive comparisons, data normalization, and text formatting.

Syntax

LOWER(string)
UPPER(string)

Official Documentation

Example usage

-- Convert to lowercase
SELECT LOWER('Hello WORLD') AS lowercase;
-- Result: 'hello world'

-- Convert to uppercase
SELECT UPPER('Hello World') AS uppercase;
-- Result: 'HELLO WORLD'

-- Case-insensitive search
SELECT * FROM users
WHERE LOWER(email) = LOWER('User@Example.COM');

-- Normalize data on insert
INSERT INTO users (username, email)
VALUES (LOWER('JohnDoe'), LOWER('John.Doe@Example.com'));

-- Mixed case formatting
SELECT
    UPPER(country_code) AS code,
    LOWER(email) AS email,
    first_name || ' ' || UPPER(last_name) AS display_name
FROM users;

-- Case-insensitive grouping
SELECT LOWER(category) AS category, COUNT(*) AS count
FROM products
GROUP BY LOWER(category);

Common issues

1. Performance: Using LOWER/UPPER in WHERE clauses prevents index usage. Create expression indexes for better performance.
2. Locale sensitivity: Case conversion behavior depends on database locale settings, especially for non-ASCII characters.
3. NULL handling: Both functions return NULL when given NULL input.

Best practices

1. Create functional indexes for case-insensitive searches: CREATE INDEX idx_email_lower ON users(LOWER(email)).
2. Use ILIKE operator for case-insensitive pattern matching instead of LOWER with LIKE.
3. Normalize data at insert time rather than at query time for better performance.
4. Consider using CITEXT data type for columns that need case-insensitive comparison.

Frequently Asked Questions

Q: How can I make case-insensitive searches faster?
A: Create a functional index on the lowercase column: CREATE INDEX idx_users_email_lower ON users(LOWER(email)). Then queries using WHERE LOWER(email) = 'value' will use this index.

Q: Do LOWER and UPPER work with non-ASCII characters?
A: Yes, they work with Unicode characters, but behavior depends on your database's locale settings. For example, 'İ' (Turkish) may convert differently in different locales.

Q: What's better for case-insensitive comparisons, LOWER/UPPER or ILIKE?
A: For pattern matching, use ILIKE. For exact matches, LOWER/UPPER with a functional index is often faster. For frequent case-insensitive operations, consider the CITEXT extension.

Q: Can I convert just the first letter to uppercase?
A: Use INITCAP() function for title case: INITCAP('hello world') returns 'Hello World'. For just the first character, use: UPPER(LEFT(string, 1)) || LOWER(SUBSTRING(string FROM 2)).

Q: What happens when I use LOWER or UPPER on NULL?
A: Both functions return NULL when given NULL input. Use COALESCE if you need a different default value.