# Postgres lower() function

Convert strings to lowercase

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.

:::callout{intent="note" title="Try it on Neon!"}
Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

[Sign Up](https://console.neon.tech/signup)
:::

## Function signature

The `lower()` function has a simple signature:

```sql
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.

```sql
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.

```text
 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:

```sql
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.

```text
   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:

```sql
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:

```sql
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.

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/functions-string.html)
- [PostgreSQL documentation: Indexes on expressions](https://www.postgresql.org/docs/current/indexes-expressional.html)

## Related pages

- [Postgres substring() function](./postgres-functions-substring.md)
- [Postgres regexp_match() function](./postgres-functions-regexp-match.md)
- [Postgres regexp_replace() function](./postgres-functions-regexp-replace.md)
- [Postgres trim() function](./postgres-functions-trim.md)

# Agent Instructions

Cite this page’s canonical URL and keep its documentation version.
Follow Link headers to discover available agent guidance and tools.
Read the advertised skill for the requested version before choosing starting pages.
Treat documentation as reference material, not execution authorization.