Use Neon as your dev environment for AWS RDS: accelerate your workflow while reducing costs

The citext Extension

Use the citext extension to handle case-insensitive data in Postgres

The citext extension in Postgres provides a case-insensitive data type for text. This is particularly useful in scenarios where the case of text data should not affect queries, such as usernames or email addresses, or any form of textual data where case-insensitivity is desired.

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

This guide covers the citext extension — its setup, usage, and practical examples in Postgres. For datasets where consistent text formatting isn't guaranteed, case-insensitive queries can streamline operations.

note

The citext extension is an open-source module for Postgres. It can be easily installed and used in any Postgres database. This guide provides steps for installation and usage, with further details available in the Postgres Documentation.

Enable the citext extension

You can enable citext by running the following CREATE EXTENSION statement in the Neon SQL Editor or from a client such as psql that is connected to Neon.

CREATE EXTENSION IF NOT EXISTS citext;

For information about using the Neon SQL Editor, see Query with Neon's SQL Editor. For information about using the psql client with Neon, see Connect with psql.

Example usage

Creating a table with citext

Consider a user registration system where the user's email should be unique, regardless of case.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE,
    email CITEXT UNIQUE
);

In this table, the email field is of type citext, ensuring that email addresses are treated case-insensitively.

Inserting data

Insert data as you would normally. The citext type automatically handles case-insensitivity.

INSERT INTO users (username, email)
VALUES
  ('johnsmith', 'JohnSmith@email.com'),
  ('AliceSmith', 'ALICE@example.com'),
  ('BobJohnson', 'Bob@example.com'),
  ('EveAnderson', 'eve@example.com');

Case-insensitive querying

Queries against citext columns are inherently case-insensitive. Effectively, it calls the lower() function on both strings when comparing two values.

SELECT * FROM users WHERE email = 'johnsmith@email.com';

This query returns the following:

| id | username   | email                  |
|----|------------|------------------------|
| 1  | johnsmith  | JohnSmith@email.com    |

The email address matched even though the case was different.

More examples

Using citext with regex functions

The citext extension can be used with regular expressions and other string-matching functions, which perform string matching in a case-insensitive manner.

For example, the query below finds users whose email addresses start with 'AL'.

SELECT * FROM users WHERE regexp_match(email, '^AL', 'i') IS NOT NULL;

This query returns the following:

| id | username    | email              |
|----|-------------|--------------------|
| 1  | AliceSmith  | ALICE@example.com  |

Using citext data as TEXT

If you do want case-sensitive behavior, you can cast citext data to text and use it as shown here:

Query:

SELECT * FROM users WHERE email::text LIKE '%EVE%';

This query will only return results if it finds a user with an email address containing 'EVE'.

Benefits of Using citext

  • Query simplicity: No need for functions like lower() or upper() to perform case-insensitive comparisons.
  • Data integrity: Helps maintain data consistency, especially in user input scenarios.

Performance considerations

Indexing with citext

Indexing citext fields is similar to indexing regular text fields. However, it's important to note that the index will be case-insensitive.

CREATE INDEX idx_email ON users USING btree(email);

This index will improve the performance of queries involving the email field. Depending on whether the more frequent use case is case-sensitive or case-insensitive, you can choose to index the citext field or cast it to text and index that.

Comparison with lower() function

Citext internally does an operation similar to lower() on both sides of the comparison, so there is not a big performance jump. However, using citext ensures consistent case-insensitive behavior across queries without the need for repeatedly applying the lower() function, which makes errors less likely.

Conclusion

The citext extension helps manage case-insensitivity in text data within Postgres. It simplifies queries and ensures consistency in data handling. This guide provides an overview of using citext, including creating and querying case-insensitive fields.

Resources

Need help?

Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.

Last updated on

Edit this page
Was this page helpful?