27

I want to achieve case insensitive uniqueness in a varchar column. But, there is no case insensitive text data type in Postgres. Since original case of text is not important, it will be a good idea to convert all to lowercase/uppercase before inserting in a column with UNIQUE constraint. Also, it will require one INDEX for quick search.

Is there any way in Postgres to manipulate data before insertion?

I looked at this other question: How to automatically convert a MySQL column to lowercase. It suggests using triggers on insert/update to lowercase text or to use views with lowercased text. But, none of the suggested methods ensure uniqueness.

Also, since this data will be read/written by various applications, lowercasing data in every individual application is not a good idea.

Community
  • 1
  • 1
user1144616
  • 1,201
  • 2
  • 15
  • 16
  • 11
    None of the current answers will actually lowercase the data before insert, which is what's being asked. See http://stackoverflow.com/questions/18807709/create-a-postgres-rule-or-trigger-to-automatically-convert-a-column-to-lowercase. – Jim Stewart Jul 17 '14 at 20:25

2 Answers2

45
ALTER TABLE your_table
  ADD CONSTRAINT your_table_the_column_lowercase_ck
  CHECK (the_column = lower(the_column));

From the manual:

The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly.

Craig R. Skinner
  • 676
  • 6
  • 5
  • 6
    I used this, because I can let my app handle putting in data in the correct lower-case format, while using the DB for what it's good for - integrity checking. – Asfand Qazi May 02 '15 at 22:50
42

You don't need a case-insensitive data type (although there is one)

CREATE UNIQUE INDEX idx_lower_unique 
   ON your_table (lower(the_column));

That way you don't even have to mess around with the original data.

  • Verified, it works exactly as I wanted it to. Stores case sensitive data (which was not required, but good to have), but enforces case-insensitive uniqueness. – user1144616 Jan 28 '12 at 16:43
  • 3
    This will still let you insert uppercase letters tho, so it shouldn't be marked as the answer. – dessalines Dec 15 '21 at 15:13