Postgres lower() function

The lower() function in Postgres is used to convert a string to lowercase.

It's commonly used for search functionality where you want case-insensitivity or when you need to standardize user input for storage or comparison purposes. For example, lower() can be used to normalize email addresses or usernames in a user management system.

Function signature

The lower() function has a simple signature:

lower(string text) -> text

• string: The input string to be converted to lowercase.

Example usage

Consider a table products with a product_name column that contains product names with inconsistent capitalization. We can use lower() to standardize these names for comparison or display purposes.

WITH products AS (
    SELECT *
    FROM (
        VALUES
            ('LAPTOP Pro X'),
            ('SmartPhone Y'),
            ('Tablet ULTRA 2')
    ) AS t(product_name)
)
SELECT lower(product_name) AS standardized_name
FROM products;

This query converts all product names to lowercase, making them consistent regardless of their original capitalization. Note that non-alphabetic characters are left unchanged.

standardized_name
-------------------
 laptop pro x
 smartphone y
 tablet ultra 2
(3 rows)

Advanced examples

Case-insensitive search

You can use lower() in a WHERE clause to perform case-insensitive searches:

WITH customers AS (
  SELECT 'John Doe' AS name, 'JOHN.DOE@EXAMPLE.COM' AS email
  UNION ALL
  SELECT 'Jane Smith' AS name, 'jane.smith@example.com' AS email
  UNION ALL
  SELECT 'Bob Johnson' AS name, 'Bob.Johnson@Example.com' AS email
)
SELECT name, email
FROM customers
WHERE lower(email) LIKE lower('%John.%');

This query will find the customer regardless of how the email address was capitalized in the database or search term.

name   |        email
----------+----------------------
 John Doe | JOHN.DOE@EXAMPLE.COM
(1 row)

Combining with other string functions

lower() can be combined with other string functions for more complex operations:

WITH user_data AS (
  SELECT 'JOHN_DOE_123' AS username
  UNION ALL
  SELECT 'JANE_SMITH_456' AS username
  UNION ALL
  SELECT 'BOB_JOHNSON_789' AS username
)
SELECT
  lower(split_part(username, '_', 1)) AS first_name,
  lower(split_part(username, '_', 2)) AS last_name,
  split_part(username, '_', 3) AS user_id
FROM user_data;

This query splits the username into parts, converts the name parts to lowercase, and keeps the user ID as-is.

Using lower() to create indexes

Postgres supports creating a functional index based on the result of a function applied to a column. To optimize case-insensitive searches, we can create an index using the lower() function:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE INDEX idx_users_name_lower ON users (lower(name));

This index will improve the performance of queries that use lower(name) to filter data.

Normalizing data for uniqueness constraints

When you want to enforce uniqueness regardless of case, you can use lower() to create a unique index on the column.

CREATE TABLE organizations (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_organizations_name_lower ON organizations (lower(name));

INSERT INTO organizations (name) VALUES ('Acme Corp');
INSERT INTO organizations (name) VALUES ('Bailey Inc.');

Trying to insert a duplicate organization name with different capitalization will raise an error:

INSERT INTO organizations (name) VALUES ('ACME CORP');
-- ERROR:  duplicate key value violates unique constraint "idx_organizations_name_lower"
-- DETAIL:  Key (lower(name))=(acme corp) already exists.

Additional considerations

Performance implications

While lower() is generally fast, using it in WHERE clauses or JOIN conditions on large tables can impact performance, as it prevents the use of standard indexes directly. In such cases, consider using functional indexes as shown in the earlier example.

Locale considerations

The lower() function uses the database's locale setting for its case conversion rules. If your application needs to handle multiple languages, you may need to consider using the lower() function with specific collations or implementing custom case-folding logic.

Alternative functions

• upper() - Converts a string to uppercase.
• initcap() - Converts the first letter of each word to uppercase and the rest to lowercase.

Resources

• PostgreSQL documentation: String functions and operators
• PostgreSQL documentation: Indexes on expressions