CODEWITHSUNDEEP
postgresql
Oleg Mikhailov
Oleg Mikhailov

Reputation: 252

Case insensitivity in PostgreSQL

How to set case insensitivity for whole PostgreSQL database or even whole server / cluster?

UPD: For column values. I just migrated database from MS SQL and now looking how to get behaviour similar to what we had before for sorting, filtering and other stuff, in most painless way.

Upvotes: 0

Views: 561

Answers (3)

phil_rawlings
phil_rawlings

Reputation: 1263

CITEXT is definitely the least painful route (as per the suggestion from @Matthew Wood)

Use the following command to install the extension to the current database:

CREATE EXTENSION IF NOT EXISTS citext;

This works great when running queries like this:

SELECT * FROM product WHERE serial_number = 'aB'

which will successfully match ab, Ab, aB or AB.

Where you may run in to trouble is if you are using parameterised SQL and letting a provider build the command for you. For example, using Npgsql (ADO.NET provider for .NET) with the following command:

SELECT * FROM product WHERE serial_number = @serial_number;

actually sends this to the server:

SELECT * FROM product WHERE serial_number = ((E'aB')::text);

This is casting the value as "text" which means it will do a case sensitive lookup. The way I managed to get round this was by modifying the Npgsql source code to add a "citext" type. Using this parameter type, the correct command would be issued:

SELECT * FROM product WHERE serial_number = ((E'aB')::citext);

Upvotes: 1

Matthew Wood
Matthew Wood

Reputation: 16417

You may be able to use the CITEXT data type instead of VARCHAR/TEXT:

CITEXT Data Type documentation

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Unquoted identifiers are case insensitive by default (and per SQL standard).

Other than that you cannot set "case insensitivity" in standard Postgres.

Upvotes: 2

Related Questions