How to make my postgresql database use a case insensitive collation?

postgres collation
postgresql 12 case-insensitive collation
postgresql case insensitive column
postgres case insensitive order by
postgresql relation does not exist case sensitive
postgresql collation support
postgres unique constraint case insensitive
postgres default collation

In several SO posts OP asked for an efficient way to search text columns in a case insensitive manner.

As much as I could understand the most efficient way is to have a database with a case insensitive collation. In my case I am creating the database from scratch, so I have the perfect control on the DB collation. The only problem is that I have no idea how to define it and could not find any example of it.

Please, show me how to create a database with case insensitive collation.

I am using postgresql 9.2.4.

EDIT 1

The CITEXT extension is a good solution. However, it has some limitations, as explained in the documentation. I will certainly use it, if no better way exists.

I would like to emphasize, that I wish ALL the string operations to be case insensitive. Using CITEXT for every TEXT field is one way. However, using a case insensitive collation would be the best, if at all possible.

Now https://stackoverflow.com/users/562459/mike-sherrill-catcall says that PostgreSQL uses whatever collations the underlying system exposes. I do not mind making the OS expose a case insensitive collation. The only problem I have no idea how to do it.


A lot has changed since this question. Native support for case-insensitive collation has been added in PostgreSQL v12. This basically deprecates the citext extension, as mentioned in the other answers.

In PostgreSQL v12, one can do:

    CREATE COLLATION case_insensitive (
      provider = icu,
      locale = 'und-u-ks-level2',
      deterministic = false
    );

    CREATE TABLE names(
      first_name text,
      last_name text
    );

    insert into names values
      ('Anton','Egger'),
      ('Berta','egger'),
      ('Conrad','Egger');

    select * from names
      order by
        last_name collate case_insensitive,
        first_name collate case_insensitive;

See https://www.postgresql.org/docs/current/collation.html for more information.

How to create Postgres DB with case insensitive collation , You should take a look at my recent answer here to a related question regarding umlauts. Basically, the solution involves a "shadow" or "search" column for� The citextextension allows you to define insensitive-case columns, so you can avoid things like the lowerfunction. In fact, this extension calls the lowerfunction automatically so that you don’t have to do it manually. If you’re using Ubuntu, you have to install the postgresql-contribpackage. $sudo apt-get install postgresql-contrib


There are no case insensitive collations, but there is the citext extension:

http://www.postgresql.org/docs/current/static/citext.html

Documentation: 9.5: citext, The citext module provides a case-insensitive character string type, citext. It won't use an index, unless you create a functional index using lower . to the database's LC_CTYPE setting (that is, as though COLLATE "default" were given). When using EF Core migrations to manage your database schema, the following in your model's OnModelCreating method configures a SQL Server database to use a case-sensitive collation: modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS"); Column collation. Collations can also be defined on text columns, overriding the database default. This can be useful if certain columns need to be case-insensitive, while the rest of the database needs to be case-sensitive.


For my purpose the ILIKE keyword did the job.

From the postgres docs:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

Documentation: 12: 23.2. Collation Support, When the database system has to perform an ordering or a character parse time as the defined collation of the function or operator expression, in case there is Use of the stripped collation names is recommended, since it will make one less in order for such collations to act in a truly case- or accent-insensitive manner,� If I have to use these features, there is more work for me converting > from SQL Server to Postgresql. I have to make more changes to my database, > and more to my software. > But, developers are generally lazy, so you could argue that this reason is > "compelling".


This is not changing collation, but maybe somebody help this type of query, where I was use function lower:

SELECT id, full_name, email FROM nurses WHERE(lower(full_name) LIKE '%bar%' OR lower(email) LIKE '%bar%')

Case sensitivity in PostgreSQL, PostgreSQL, unlike MySQL, treats strings as case sensitive in all circumstances. use the citext extension; use ILIKE instead of LIKE; use Postgres' lower() Popular authentication libraries (like Clearance and Devise) already do this for you. In order to query case sensitive, you can use the mysql COLLATE clause. Note that while this system allows creating collations that “ ignore case ” or “ ignore accents ” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be


I believe you need to specify your collation as a command line option to initdb when you create the database cluster. Something like

initdb --lc-collate=en_US.UTF-8 

It also seems that using PostgreSQL 9.3 on Ubuntu and Mac OS X, initdb automatically creates the database cluster using a case-insensitive collation that is default in the current OS locale, in my case, en_US.UTF-8.

Could you be using an older version of PostgreSQL that does not default to the host locale? Or could it be that you are on an operating system that does not provide any case-insensitive collations for PostgreSQL to choose from?

Using insensitive-case columns in PostgreSQL with citext, It still is a pity I can not simply flip a switch on the database to make everything case insensitive by default. Migration from MySQL or MSSQL� The citext type. The older PostgreSQL method for performing case-insensitive text operations is the citext type; it is similar to the text type, but operators are functions between citext values are implicitly case-insensitive.


Collations and Case Sensitivity, PostgreSQL is a case-sensitive database by default, but provides various ICU collations, so it is now possible to use collations in a more flexible way. To create a collation, place the following in your context's OnModelCreating : select * from stocks where symbol = 'AADBX'. and. select * from stocks where symbol = 'aadbx'. would bring up the same result set. Potentially on some systems it'd be possible to. generate a case insensitive collation as part of a locale. and then use such for LC_COLLATE on initdb which would make all.


Collations and case sensitivity, How to configure collations and case-sensitivity in the database and on queries. (e.g. Sqlite, PostgreSQL), others are case-insensitive (SQL Server, MySQL). case sensitivity, or more generally, collations, and how to do so in an When using EF Core migrations to manage your database schema, the� By using citext as the variable type we create a column that is case insensitive for comparisons. CREATE TABLE customer2 (custsysid serial NOT NULL, firstname character varying (20), lastname citext, CONSTRAINT pkcustomer2 PRIMARY KEY (custsysid)) WITH (OIDS=FALSE);


general - Case insensitive collation, Therefore the question: are there plans to create a set of case Not from the Postgres project -- we just use the collations supplied by be built into the collation, but apparently MS built case sensitivity in the database engine. PostgreSQL breaks ties using a byte-wise comparison. Comparison that is not deterministic can make the collation be, say, case- or accent-insensitive. For that, you need to choose an appropriate LC_COLLATE setting and set the collation to not deterministic here. Nondeterministic collations are only supported with the ICU provider.